Lindorm:CREATE INDEX

Index key definition (index_key_definition)

For secondary indexes, each key definition can be:

• A column name, optionally with DESC for descending order

• A function expression (Z-ORDER, S2, CAST, MD5, or SHA256)

For search indexes, each key definition can be:

• A column name with optional index key properties

• The wildcard constant * to index all existing columns

CREATE INDEX IF NOT EXISTS idx5 USING SEARCH ON test(*);

Secondary index properties

Search index properties

Build an index asynchronously

The default mode is ASYNC. The statement returns immediately.

CREATE INDEX idx1 ON test(c1 DESC) INCLUDE(c3, c4) WITH (COMPRESSION='ZSTD');

Verify the result:

SHOW INDEX FROM test;

Create a composite index synchronously

CREATE INDEX idx1 ON test(c1, c2, c3) INCLUDE(c4) SYNC WITH (COMPRESSION='ZSTD');

Verify the result:

SHOW INDEX FROM test;

Create a spatio-temporal secondary index

CREATE INDEX idx ON roads (Z-ORDER(g1));
CREATE INDEX idt ON roads (Z-ORDER(g1, t));

See Spatio-temporal indexes.

Include all predefined columns

CREATE INDEX idx1 ON test(c4 DESC) WITH (INDEX_COVERED_TYPE='COVERED_ALL_COLUMNS_IN_SCHEMA');

Verify the result:

SHOW INDEX FROM test;

Include all dynamic columns

CREATE INDEX idx1 ON test(c4 DESC) WITH (INDEX_COVERED_TYPE='COVERED_DYNAMIC_COLUMNS');

Verify the result:

SHOW INDEX FROM test;

Set pre-partitions for the index table

Create an index with 32 pre-partitions.

CREATE INDEX idx1 ON test(c4 DESC) INCLUDE(c5, c6) WITH (NUMREGIONS='32');

Verify the result:

SHOW INDEX FROM test;

Set start and end keys with pre-partitions

Create an index table with 32 pre-partitions between 11111111 and 9999999.

CREATE INDEX idx1 ON test(c3 DESC) INCLUDE(c5, c6)
WITH (NUMREGIONS='32', STARTKEY='11111111', ENDKEY='9999999');

Verify the result:

SHOW INDEX FROM test;

Create a Z-ORDER secondary index

CREATE INDEX idx1 ON test(Z-ORDER(c5));

Verify the result:

SHOW INDEX FROM test;

Create an S2 grid secondary index

S2 indexes only support asynchronous building. Run BUILD INDEX to trigger the build after creating the index.

CREATE INDEX idx1 ON test(S2(c5, 10));
BUILD INDEX s2_idx ON test;

Verify the result:

SHOW INDEX FROM test;

Index a column after a data type conversion

Convert column c3 to INTEGER, then create a secondary index on the result.

CREATE INDEX idx1 ON test(CAST(c3 AS INTEGER));

Verify the result:

SHOW INDEX FROM test;

Build a search index asynchronously

CREATE INDEX IF NOT EXISTS idx2 USING SEARCH ON test(p1, p2, c1, c2, c3);

Verify the result:

SHOW INDEX FROM test;

To monitor build progress, see View the full build progress of a search index.

Index all columns

If no column properties are specified, default values apply.

CREATE INDEX IF NOT EXISTS idx2 USING SEARCH ON test('*');

To monitor build progress, see View the full build progress of a search index.

Add index key properties

Index all columns and configure column c3 for IK tokenization:

CREATE INDEX IF NOT EXISTS idx2 USING SEARCH ON test('*', c3(type=text, analyzer=ik, indexed=true));

To use Elasticsearch-compatible custom mapping for column c3:

CREATE INDEX IF NOT EXISTS idx2 USING SEARCH ON test('*', c3(mapping='{
  "type": "text",
  "analyzer": "ik_max_word"
}'));

To monitor build progress, see View the full build progress of a search index.

Set the index state and shard count

CREATE INDEX IF NOT EXISTS idx2 USING SEARCH ON test(c1, c3(type=text, analyzer=ik))
WITH (indexState=ACTIVE, numShards=4);

To monitor build progress, see View the full build progress of a search index.

Set custom index settings

Create a search index with ZSTD compression and a 10-second refresh interval:

CREATE INDEX IF NOT EXISTS idx2 USING SEARCH ON test(c1, c3(type=text, analyzer=ik))
WITH (indexState=ACTIVE, INDEX_SETTINGS='{
  "index": {
    "codec": "zstd",
    "refresh_interval": "10s"
  }
}');

To monitor build progress, see View the full build progress of a search index.

Create a time-partitioned search index

Partition by column c4, starting from 30 days ago, with a new partition every 7 days and a 90-day retention period.

CREATE INDEX IF NOT EXISTS idx2 USING SEARCH ON test(c1, c2, c3, c4)
PARTITION BY RANGE TIME(c4) PARTITIONS 16
WITH (
  indexState=ACTIVE,
  RANGE_TIME_PARTITION_START='30',
  RANGE_TIME_PARTITION_INTERVAL='7',
  RANGE_TIME_PARTITION_TTL='90',
  RANGE_TIME_PARTITION_MAX_OVERLAP='90'
);

To monitor build progress, see View the full build progress of a search index.

Store raw data in the search index

By default, search indexes filter data but do not store raw column values. Enable raw data storage to query directly through the search engine.

Store all index columns:

CREATE INDEX idx2 USING SEARCH ON test(c1, c2, c3, c4)
WITH (SOURCE_SETTINGS='{
  "enabled": true
}');

Verify the result:

SHOW INDEX FROM test;

Store a subset of columns (c2, c3, c4, excluding c1):

CREATE INDEX idx2 USING SEARCH ON test(c1, c2, c3, c4)
WITH (SOURCE_SETTINGS='{
  "includes": ["c*"],
  "excludes": ["c1"]
}');

Verify the result:

SHOW INDEX FROM test;

To monitor build progress, see View the full build progress of a search index.