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.
Backend API Indexes
By default, indexes only affect offline queries in the app. To use indexes for OnlineDB or CloudCode queries or the backend API, the
cloud_indexes
feature flag must be enabled in the app's Settings in OXIDE.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.
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.<data-model>
<model name="asset" label="Asset">
<field name="make" label="Make" type="text:name"/>
<field name="model" label="Model" type="text:name"/>
<field name="serial_number" label="Serial Number" type="text:number"/>
<field name="condition" label="Condition" type="single-choice">
<option key="good">Good</option>
<option key="ok">Ok</option>
<option key="bad">Bad</option>
</field>
<belongs-to model="building"/>
<index on="building" />
<index on="make,model" name="by_model" />
<display>{make} {model} {serial_number}</display>
</model>
<model name="building" label="Building">
<field name="name" label="Name" type="text:name"/>
<has-many model="asset" name="assets"/>
<display>{name}</display>
</model>
</data-model>
Query | Which index is used? | Details |
---|---|---|
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. |
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:
dialog('query', JSON.stringify(DB.archive.where('make = ? and model = ?', 'Apple', 'iPad').explain()));
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:
{
"type":"index",
"results":2,
"scanned":1245,
"duration":8,
"ranges":[{"type":"asset","index":"by_model",
"lowerBound":["Apple", "iPad"],
"upperBound":["Apple", "iPad"],
"excludeLower":false,"excludeUpper":false}]
}
On native containers on Android, iOS and Windows, SQLite is used, and the
explain()
output returns the underlying SQLite query and query plan instead.For more technical information on specifying indexes, please refer to the index reference documentation.
Last modified 1yr ago