App Indexes
Indexing provides a way to significantly speed up various types of queries in apps. JourneyApps allows indexes to be defined for the app database in the data model.
Indexes specifically help for cases where the query filters out a large percentage of the results. For, example, if there are 20 000 asset objects on a device, and a query only returns 5 results, indexing can speed up the query significantly. On the other hand, if the query returns 10 000 results (before skipping and limiting), indexing will not really improve the performance.
Indexes may also help for sorting results more efficiently.
Performance trade-off
Specifying unnecessary indexes can affect the performance of the app negatively. Indexes generally make writes slower, and use more storage.
Example Data Model
In this example, indexes are used on the belongs-to relationship between asset and building, and also on the make and model for when they are queried together.
Example queries
DB.asset.where('building = ?', building)
DB.asset.where('building_id = ?', building_id) building.assets
building
These are all different forms of the same query, and use the same index.
DB.asset.where('condition = ?, 'ok')
None
condition doesn’t have any indexes specified in the data model.
DB.asset.where('condition = ? and building = ?', 'ok', building) or
building.assets.where('serial_number = ?', 'AB123')
building
building is the only indexed field here.
DB.asset.where('make = ? and model = ?', 'Apple', 'iPad')
make,model
Since the index of make,model covers both fields, it is used.
DB.asset.where('make = ?', 'Apple')
make,model
(Advanced) The first part of the index can be used for the query.
DB.asset.where('model = ?', 'iPad')
None
(Advanced) Since ‘make’ isn’t specified in the query, the ‘make,model’ index cannot be used.
DB.asset.where('(make = ? and model = ?) or (building = ?)', 'Apple', 'iPad', building)
make,model and building
(Advanced) The make,model index is used for the first part of the query, and the building index is used for the second part of the query.
asset.building()
Not applicable
A belongs-to lookup does not need an index.
Debugging
During development, to test whether a query is using an index or not, you can use the explain() function with JSON.stringify() to display the result in a dialog in the app.
Here is an example:
The results of the above dialog will include the following results when running in a browser:
type: ‘index’ or ‘full scan’scanned: number of objects returned by the index before further filteringresults: number of results returned after filteringranges: which index is used, along with its boundsduration: duration of the query in milliseconds
For example, for the above query:
On native containers on Android, iOS and Windows, SQLite is used, and the explain() output returns the underlying SQLite query and query plan instead.
Reference Documentation
Last updated