INDEX

The INDEX construct is used to define a secondary index in a row-oriented table:

CREATE TABLE `<table_name>` (
  ...
    INDEX `<index_name>`
    [GLOBAL|LOCAL]
    [SYNC|ASYNC]
    [USING <index_type>]
    ON ( <index_columns> )
    [COVER ( <cover_columns> )]
    [WITH ( <parameter_name> = <parameter_value>[, ...])]
  [,   ...]
)

where:

• GLOBAL/LOCAL — global or local index, depending on the index type (<index_type>), only one of them may be available:

  • GLOBAL — an index implemented as a separate table or a set of tables. Synchronous update of such an index requires distributed transactions.
  • LOCAL — a local index within a shard of a columnar or row-based table, does not require distributed transactions during update, but does not provide pruning during search.

• <index_name> — unique index name by which data can be accessed.

• SYNC/ASYNC — indicator of index synchrony.

  • SYNC - synchronous index. Default value.
  • ASYNC - asynchronous index.

• UNIQUE — indicator of a unique secondary index. A unique index must be global synchronous (GLOBAL UNIQUE SYNC) and must not contain the USING <index_type> construct.

• <index_type> - index type, currently supported:

  • secondary — secondary index. Only the GLOBAL mode is available for secondary indexes. This is the default index type.
  • vector_kmeans_tree — vector index. More details are described in the Vector index section.
  • fulltext_plain — basic full-text index. More details are described in Fulltext index.
  • fulltext_relevance — full-text index with BM25 statistics for relevance calculation. More details are described in Fulltext index.
  • json — JSON index to speed up predicates JSON_EXISTS and JSON_VALUE on a column of type Json or JsonDocument. More details are described in JSON-index.
  • bloom_filter — local Bloom index. Only LOCAL is available. See ALTER TABLE ADD INDEX.
  • bloom_ngram_filter — local N-gram Bloom index. Only LOCAL is available. See ALTER TABLE ADD INDEX.

• <index_columns> — comma-separated list of column names of the table being created, which determines the composition and order of columns included in the index key. Must be specified. The index key will consist of these columns with the addition of the table's primary key columns.

• <cover_columns> — comma-separated list of column names of the table being created that will be stored in the index in addition to the index key columns, allowing you to get additional data without accessing the table. Empty by default.

• <parameter_name> and <parameter_value> are index parameters specific to a particular <index_type>.

Example

CREATE TABLE my_table (
    a Uint64,
    b Bool,
    c Utf8,
    d Date,
    INDEX idx_d GLOBAL ON (d),
    INDEX idx_ba GLOBAL ASYNC ON (b, a) COVER (c),
    PRIMARY KEY (a)
)