Index fields to speed up filters and searches

Learn how to use indexes in Ninox and maintain performance in large apps.

Indexes help Ninox find matching records without scanning an entire table. Queries can repeatedly scan whole tables, even when they return few records. This affects performance across an app and its cloud environment.

This often happens when:

• A table contains 20,000 or more records.
• A query runs hundreds or thousands of times.
• The filtered field has no index.

The impact grows when several users run these queries simultaneously.

Apply an index to a field

You can enable indexing in Behavior under the field's Settings. Get more details about it here.

This creates a lookup structure that allows the database to find records efficiently.

When to use indexing

Index a field when all these conditions apply:

• The table contains thousands of records.
• The field is used in where conditions.
• The query runs frequently in views, dashboards, scripts, or automations.

For example, the "Tasks" table is large and queried frequently for dashboards. Each task has a unique code in the "Reference" field. A "SearchReference" field provides the value to look up.

let mySearch := search_reference;
select tasks where reference = mySearch

Indexing the "Reference" field improves this query. Fields with distinct values are strong candidates. A choice field usually contains repeated values, which reduces an index's benefit.

For dashboards in large, multi-user apps, also use client-side select statements.

When to avoid indexing

Do not index every field. Indexes use extra storage and memory. They also increase the time needed to write records.

Avoid indexing when:

• The table is small or the field is rarely filtered.
• The query does not run frequently.
• Most records share the same value.
• The field has few possible values, such as a Yes/No field.

How indexing improves performance

Consider these queries:

select tasks where status = 1

select tasks where assigned_user = user()

Without an index, Ninox checks each record against the condition. A table with 20,000 records needs 20,000 evaluations for each query. If the query runs 5,000 times, it needs 100 million evaluations.

With an index, Ninox looks up the value, retrieves matching record IDs, then loads only those records. This reduces the number of records that Ninox needs to scan.

Requirements for using an index

Creating an index is not enough. The query must meet all these requirements:

• Use the indexed field directly. Do not wrap it in a function or reference it through a logic field.
• Use =, >, >=, <, or <=.
• Compare the field with a literal or a let variable. Do not compare it directly with another field or a calculated value.

The search value must be available before the scan begins. A variable is evaluated once before scanning. A field reference can change for each scanned record, so Ninox cannot use the index.

This query uses the index:

let searchValue := search_reference;
select tasks where reference = searchValue

This query does not use the index:

let zz := this;
select tasks where reference = zz.search_reference

The indexed condition must be the first condition in select and stand on its own. Apply further conditions to the select result.

Query examples

Functions prevent indexing

Applying a function to an indexed field prevents the index from being used:

select tasks where upper(status) = "OPEN";
select tasks where some_function(status)

Ninox must scan all records because it does not use the field directly.

Use separate branches instead of or

An or condition prevents index usage:

let searchValue := search_term;
select tasks where reference = searchValue or title = searchValue

Use one branch for each criterion:

let searchValue := search_term;
if search_reference then
select tasks where reference = searchValue
else if search_title then
select tasks where title = searchValue
else
null
end;

Apply additional conditions after the indexed select

A select with combined conditions scans the full table. It cannot use an index, even when field1 is indexed:

let c1 := condition1;
let c2 := condition2;
let c3 := condition3;
select tasks where (not c1 or c1 = field1) and (not c2 or c2 = field2) and (not c3 or c3 = field3)

When a criterion uses an indexed field, use that condition alone in the select. Then filter its result:

if c1 then
(select tasks where field1 = c1)[(not c2 or field2 = c2) and (not c3 or field3 = c3)]
else
select tasks where (not c2 or c2 = field2) and (not c3 or c3 = field3)
end;

not c1 also treats 0 as no condition. Check this behavior for numeric or choice criteria.

Use a range for prefix search

Two conditions on the same indexed field using >= and <= create one range scan. Calculate the upper bound in a variable:

let p := search_reference;
let pEnd := p + "zzzz";
select tasks where reference >= p and reference <= pEnd

Do not use indexes for substring search

Indexes order values from their first character. They can find a full value or prefix. They cannot find text inside a value.

contains() and like never use an index. Store values in a clean, normalized format instead. Use one value per indexed field and search with =.

Avoid selects in logic fields

A select in a logic field runs again whenever you read it. It also runs whenever a record in the queried table changes. do as server only decides where the query runs. It does not change how often it runs.

Put the condition directly in the select. Do not filter a logic field result in memory.