---
metadata:
  - name: generator
    content: Diplodoc Platform v5.57.3
alternate:
  - https://ydb-platform--ydb.viewer.diplodoc.com/en/dev/min_max-skip-index.md
  - https://ydb-platform--ydb.viewer.diplodoc.com/ru/dev/min_max-skip-index.md
  - href: https://ydb-platform--ydb.viewer.diplodoc.com/en/dev/min_max-skip-index.md
    type: text/markdown
    title: Markdown version
  - href: https://ydb-platform--ydb.viewer.diplodoc.com/en/llms.txt
    type: text/markdown
    title: llms.txt
sourcePath: en/core/dev/min_max-skip-index.md
---
> **Documentation Index:** Fetch the complete configuration index at https://ydb-platform--ydb.viewer.diplodoc.com/en/llms.txt

# min_max index

min_max index is a [local index](https://ydb-platform--ydb.viewer.diplodoc.com/en/concepts/glossary.md#local-index) that speeds up scanning queries with a highly selective filter by skipping fragments. Unlike global [secondary indexes](https://ydb-platform--ydb.viewer.diplodoc.com/en/concepts/glossary.md#secondary-index), it acts as a read filter for the base table and reduces the amount of data that actually needs to be read.

For each indexed data fragment, the min_max index stores the minimum and maximum value of one column. During query execution, YDB evaluates the predicate on these two values. If the evaluation results show that the predicate will filter out all tuples of the fragment, the fragment is skipped.

## Examples {#examples}

Creation syntax: [CREATE TABLE](https://ydb-platform--ydb.viewer.diplodoc.com/en/yql/reference/syntax/create_table/min_max_index.md), [ALTER TABLE ADD INDEX](https://ydb-platform--ydb.viewer.diplodoc.com/en/yql/reference/syntax/alter_table/indexes.md#local-min-max).

Creating a columnar table with a min_max index:


```yql
CREATE TABLE events (
    id Uint64,
    created_at Timestamp,
    level Int32,
    resource_id Utf8,
    PRIMARY KEY (id),
    INDEX idx_created_at LOCAL USING min_max
        ON (created_at),
    INDEX idx_level LOCAL USING min_max
        ON (level)
)
WITH (
    STORE = COLUMN
);
```


Adding a min_max index to an existing columnar table:


```yql
ALTER TABLE events
  ADD INDEX idx_resource_id LOCAL USING min_max
  ON (resource_id);
```


Queries with range predicates can use the index to skip non-matching fragments:


```yql
SELECT id, resource_id
FROM events
WHERE created_at BETWEEN Timestamp("2024-01-01T00:00:00.000000Z")
                     AND Timestamp("2024-01-02T00:00:00.000000Z");
```


## When to use {#use}

The min_max index is useful when the values of the indexed column change little between rows that are adjacent in the primary key order: for example, timestamps, monotonically increasing identifiers, or other values correlated with the primary key.

The min_max index can also be useful when the filter predicate selects a very small fraction of the data (on the order of one row per million). For example, when querying a service log table and keeping only records with the `ERROR` level: if the service writes one error per 1,000,000 records, the min_max index will likely significantly reduce the read volume.

## Features and limitations {#limitations}

<!-- source: en/yql/reference/syntax/_includes/min_max_index_features.md -->
* The index is always [local](https://ydb-platform--ydb.viewer.diplodoc.com/en/concepts/glossary.md#local-index) (`LOCAL`); there is no [global](https://ydb-platform--ydb.viewer.diplodoc.com/en/concepts/glossary.md#secondary-index) variant.
* Queries do not use the `VIEW <index>` syntax (unlike, for example, [full-text indexes](https://ydb-platform--ydb.viewer.diplodoc.com/en/dev/fulltext-indexes.md)).
* The filter is applied during reads only to data fragments for which the minimum and maximum values of the indexed column have already been computed and stored with the table data at write time or [merge](https://ydb-platform--ydb.viewer.diplodoc.com/en/concepts/glossary.md#compaction). For other fragments, no skip by this index is performed.
<!-- endsource: en/yql/reference/syntax/_includes/min_max_index_features.md -->

{% note info "Limitations" %}

<!-- source: en/yql/reference/syntax/_includes/min_max_index_limitations.md -->
* Supported only for [columnar tables](https://ydb-platform--ydb.viewer.diplodoc.com/en/concepts/glossary.md#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 {#see-also}

* [Secondary indexes](https://ydb-platform--ydb.viewer.diplodoc.com/en/dev/secondary-indexes.md)
* [Local indexes](https://ydb-platform--ydb.viewer.diplodoc.com/en/concepts/query_execution/local_indexes.md)
* [YQL reference: CREATE TABLE](https://ydb-platform--ydb.viewer.diplodoc.com/en/yql/reference/syntax/create_table/min_max_index.md)
* [YQL reference: SELECT](https://ydb-platform--ydb.viewer.diplodoc.com/en/yql/reference/syntax/select/index.md)
* [YQL reference: ALTER TABLE](https://ydb-platform--ydb.viewer.diplodoc.com/en/yql/reference/syntax/alter_table/indexes.md#local-min-max)
* [Quick start](https://ydb-platform--ydb.viewer.diplodoc.com/en/recipes/min_max-skip-index/min_max-skip-index-quickstart.md)