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.
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.
<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>
Example queries
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.
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:
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 filtering
results: number of results returned after filtering
ranges: which index is used, along with its bounds
duration: 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.
Reference Documentation
For more technical information on specifying indexes, please refer to the index reference documentation.

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.

Last modified 1yr ago