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]
    [UNIQUE]
    [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 set of tables. Synchronous updates to such an index require distributed transactions.
  • LOCAL — a local index within a shard of a row-oriented or column-oriented table. Does not require distributed transactions for updates, but does not provide pruning during search.

• <index_name> — unique index name that will be used to access data.

• SYNC/ASYNC — synchronous or asynchronous writes to the index, synchronous by default.

• <index_type> - index type, currently supported:

  • secondary — secondary index. Only GLOBAL is available. This is the default value.
  • vector_kmeans_tree — vector index. Described in detail in Vector index.

• UNIQUE — creates an index with uniqueness guarantee for inserted values.

• <index_columns> — comma-separated list of column names from the created table that can be used for index searches. Must be specified.

• <cover_columns> — comma-separated list of column names from the created table that will be saved in the index in addition to search columns, providing the ability to get additional data without accessing the table. Empty by default.

• <parameter_name> and <parameter_value> — 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),
    INDEX idx_bc GLOBAL UNIQUE SYNC ON (b, c),
    PRIMARY KEY (a)
)