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.