> For the complete documentation index, see [llms.txt](https://docs.ninox.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.ninox.com/ninox-scripting/automate-your-workflows/automations/index-fields-to-speed-up-filters-and-searches.md).
# 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](/builder-hub/design-your-app/add-and-edit-data-fields-in-your-tables/all-data-fields/shared-field-settings.md).
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.
```none
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:
```none
select tasks where status = 1
```
```none
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:**
```none
let searchValue := search_reference;
select tasks where reference = searchValue
```
**This query does not use the index:**
```none
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:
```none
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:
```none
let searchValue := search_term;
select tasks where reference = searchValue or title = searchValue
```
Use one branch for each criterion:
```none
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:
{% code overflow="wrap" %}
```none
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)
```
{% endcode %}
When a criterion uses an indexed field, use that condition alone in the select. Then filter its result:
{% code overflow="wrap" %}
```none
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;
```
{% endcode %}
`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:
```none
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.
---
# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.
## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.
Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:
```
GET https://docs.ninox.com/ninox-scripting/automate-your-workflows/automations/index-fields-to-speed-up-filters-and-searches.md?ask=&goal=
```
`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.