---
metadata:
  - name: generator
    content: Diplodoc Platform v5.56.0
alternate:
  - https://ydb.tech/docs/en/concepts/query_execution/local_indexes.md?version=main
  - https://ydb.tech/docs/ru/concepts/query_execution/local_indexes.md?version=main
  - href: en/concepts/query_execution/local_indexes.md
    type: text/markdown
    title: Markdown version
  - href: ../../llms.txt
    type: text/markdown
    title: llms.txt
sourcePath: en/core/concepts/query_execution/local_indexes.md
---
> **Documentation Index:** Fetch the complete configuration index at https://ydb.tech/docs/en/llms.txt

# Local indexes

## Local index concept

A [local index](https://ydb.tech/docs/en/concepts/glossary.md?version=main#local-index) is an auxiliary structure that is stored together with the table data and is used when reading from storage. It does not materialize a separate index table.

Local indexes act as read filters for the main table: the [query optimizer](https://ydb.tech/docs/en/concepts/query_execution/optimizer.md?version=main) and the storage layer use them to skip irrelevant data fragments during scanning.

Currently, YDB implements local [Bloom indexes](#bloom-skip-indexes) and a [min_max index](#min-max-index).

## Bloom indexes {#bloom-skip-indexes}

Bloom indexes are a special case of a [local index](https://ydb.tech/docs/en/concepts/glossary.md?version=main#local-index) built on the [Bloom filter](https://en.wikipedia.org/wiki/Bloom_filter).

When reading, for each data fragment the index checks whether the sought value (or a set of n-grams) can occur in it. If the filter reports that the value definitely does not occur, the fragment is skipped without reading the indexed columns. If the filter “passes” the check, the value may be present — including due to a false positive — and the fragment must be read. This reduces the amount of data actually read for selective queries.

### Bloom index types

* `bloom_filter` — builds a filter on the exact values of the indexed column. Suitable for equality conditions (`=`), list membership checks (`IN`), and other equality comparisons for supported types.
* `bloom_ngram_filter` — builds a filter on n-grams of a string column (`String`, `Utf8`). When searching by substring or pattern (`LIKE`), the query is split into n-grams; if the fragment index lacks at least one of the required n-grams, the sought substring cannot be in it, and the fragment is skipped. Supported only in [columnar tables](https://ydb.tech/docs/en/concepts/glossary.md?version=main#column-oriented-table).

### Local Bloom indexes

Type `bloom_filter` works in both [row-based](https://ydb.tech/docs/en/concepts/glossary.md?version=main#row-oriented-table) (OLTP) and [columnar](https://ydb.tech/docs/en/concepts/glossary.md?version=main#column-oriented-table) (OLAP) tables, but the implementation differs:

* In row-based tables, the filter is built as a prefix Bloom filter on the left prefix of the [primary key](https://ydb.tech/docs/en/concepts/glossary.md?version=main#primary-key). The indexed columns must form a continuous leading subset of the primary key columns. This speeds up point reads and range scans that restrict the leading key columns. To create a prefix Bloom filter, use [ALTER TABLE ... ADD INDEX](https://ydb.tech/docs/en/yql/reference/syntax/alter_table/indexes.md?version=main#local-bloom); to drop it, use [ALTER TABLE ... DROP INDEX](https://ydb.tech/docs/en/yql/reference/syntax/alter_table/indexes.md?version=main#drop-index).
* In columnar tables, the filter is built on the values of the indexed column in each data fragment (portion) and is used in analytical scans to skip fragments that do not contain the sought value.

{% note info "Limitations" %}

<!-- source: en/yql/reference/syntax/_includes/bloom_skip_index_limitations.md -->
* `COVER (...)` and extra index columns are not supported.
* Column-oriented tables allow only one indexed column. Row-oriented tables allow multiple indexed columns.
* `bloom_ngram_filter` is not supported on row-oriented tables.
* On row-oriented tables, the indexed columns of a `bloom_filter` must be a left prefix of the [primary key](https://ydb.tech/docs/en/concepts/glossary.md?version=main#primary-key). Non-prefix column sets are rejected.
* On row-oriented tables, two `bloom_filter` indexes cannot share the same prefix length (the same set of leading primary-key columns).
<!-- endsource: en/yql/reference/syntax/_includes/bloom_skip_index_limitations.md -->

{% endnote %}

### Additional materials

* [Bloom indexes](https://ydb.tech/docs/en/dev/bloom-skip-indexes.md?version=main)
* [ALTER TABLE ADD INDEX](https://ydb.tech/docs/en/yql/reference/syntax/alter_table/indexes.md?version=main#local-bloom)

## min_max index {#min-max-index}

A min_max index is a special case of a [local index](https://ydb.tech/docs/en/concepts/glossary.md?version=main#local-index) that stores the minimum and maximum value of one indexed column for each data fragment.

When reading with a [special kind of filter](#min-max-index-predicates) on a column with a min_max index, YDB first reads the minimum and maximum values stored for the fragment and checks whether the filter interval intersects this range. If the intervals do not intersect, the predicate is guaranteed to be false on all values of the fragment, so the fragment can be skipped. This is useful for range predicates and equality conditions (a special case of a range predicate) on columns with a small spread between the minimum and maximum values within stored fragments.

Example:

In storage, for the [`events` table](https://ydb.tech/docs/en/yql/reference/syntax/create_table/min_max_index.md?version=main#example), one of the fragments of column `level` of type `Int32` contains values `[5, 5, 9, 5, 9, 13]`. Then the minimum value is 5, the maximum is 13. For query `SELECT * FROM events WHERE level = 15`, the filter interval is `[15, 15]`. It does not intersect with interval `[5, 13]`, so such a fragment does not need to be read from storage.

### Min-max index predicates {#min-max-index-predicates}

The optimizer can use the min_max index for predicates `=`, `<`, `<=`, `>`, `>=`, `BETWEEN`, as well as compatible combinations with `AND` or `OR`.

{% note info "Limitations" %}

<!-- source: en/yql/reference/syntax/_includes/min_max_index_limitations.md -->
* Supported only for [columnar tables](https://ydb.tech/docs/en/concepts/glossary.md?version=main#column-oriented-table).
* `ON (...)` must specify exactly one column.
* `COVER (...)` and additional data columns are not supported.
* Specific parameters of `WITH (...)` are not supported.
* `ALTER INDEX` is not supported for the min_max index.
* Columns of types `Json` and `JsonDocument` are not supported.
<!-- endsource: en/yql/reference/syntax/_includes/min_max_index_limitations.md -->

{% endnote %}

### Additional materials

* [min_max index](https://ydb.tech/docs/en/dev/min_max-skip-index.md?version=main)
* [ALTER TABLE ADD INDEX](https://ydb.tech/docs/en/yql/reference/syntax/alter_table/indexes.md?version=main#local-min-max)