JayData.org

Database indices with indexedDb and WebSql/sqLite

Author: Gábor Dolla March 14th, 2013

, , , ,


This feature is only available in the Pro version of WebSQL/SQLite and IndexedDb providers.

You can download your Pro provider from jaystack.com after a simple registration. For commercial usage you can either evaluate it for 30 days for free or you can buy it. For non-commercial usage it’s free.

Indexes on database tables are vital, they provide rapid random lookups as well as efficient access of ordered records. Unique indices create implicit constraints on the underlying table. Without indices or when a query can not use existing indices, the database must perform a full table scan, which can be slow, especially with lots of records. So proper indexing makes the difference between a performing app and a slowish one. On the other hand, having too many indices can slow down the inserts/updates and might take up considerable amount of disc space!

This feature works with the Pro version of indexedDb and WebSQL/SQLite providers. The appropriate provider must be manually included, autoloading does not work for Pro drivers. The inclusion must be before the first context is created.

Indexes must be declared on the EntityContext level. Entities might have many indices.

Each index definition is an object, with the following properties:

name: name of the index

keys: array containing the name of the columns

unique: boolean, optional, default: false, true –> unique index

Limitation: Internet Explorer 10 with indexedDb does not support indices with more than one column!

WebSQL

WebSQL(sqLite) handles the indices automatically.

IndexedDb

The JayData indexedDb provider must explicitly select an index to be used for retrieving data from the database. Therefore it is important to match your filters and your index declarations, if JayData indexedDb provider can not match an index to the filter then it will do a full table scan.

For a simple filter (ex: it.Name2 == ‘Foo’) it’s straightforward, there should be an index for the ‘Name2’ field. In the previous example there is no index for ‘Name2’ so JayData will do a full table scan!

When the filter is complex with ‘and’ and ‘or’ logical operators, then JayData first builds a tree from the query. Every ‘or’ creates a new subquery and after every subqueries are executed the results are combined in memory. Subqueries might contain ‘and’ logical operations. For each subquery, JayData takes the first member of the subquery and tries to find an index for that field! The rest of the filtering is done in memory. In the light of the previous example, the following filter would be fast:

while this filter would do a full table scan:


, , , ,