Lindorm:CREATE INDEX

Secondary index

Search index

Examples related to secondary indexes

Create a secondary index asynchronously

By default, if you do not specify the creation method, the index is created asynchronously.

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

Verify the result

You can execute the SHOW INDEX FROM test; statement to check whether the index is created.

Create a federated index

Create a federated index for the c1, c2, and c3 columns.

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

Verify the result

You can execute the SHOW INDEX FROM test; statement to check whether the index is created.

Include all columns in the index

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

Verify the result

You can execute the SHOW INDEX FROM test; statement to check whether the index is created.

Include all dynamic columns in the index

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

Verify the result

You can execute the SHOW INDEX FROM test; statement to check whether the index is created.

Configure the estimated number of partitions of the index table

Set the estimated number of partitions of the index table to 32.

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

Verify the result

You can execute the SHOW INDEX FROM test; statement to check whether the index is created.

Specify the start and end keys of the index table

Specify that the index table is split into 32 partition in the range from the start key 11111111 to the end key9999999.

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

Verify the result

You can execute the SHOW INDEX FROM test; statement to check whether the index is created.

Create a Z-ORDER secondary index

Create a Z-ORDER secondary index for the c5 column that stores POINT data.

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

Verify the result

You can execute the SHOW INDEX FROM test; statement to check whether the index is created.

Create an S2 secondary index

1. Create an S2 secondary index for the c5 column that stores POINT data. The index can be created only in asynchronous mode.

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

2. Build the S2 secondary index.

   BUILD INDEX s2_idx ON test;

Verify the result

You can execute the SHOW INDEX FROM test; statement to check whether the index is created.

Convert the data type of the specified column

Create a secondary index for the c3 column after converting its data type to INTEGER.

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

Verify the result

You can execute the SHOW INDEX FROM test; statement to check whether the index is created.

Examples related to search indexes

Create a secondary index asynchronously

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

Verify the result

You can execute the SHOW INDEX FROM test; statement to check whether the index is created.

Create a search index on all columns

Create a search index for all columns without specifying column attributes. In this case, the default values of all column attributes are retained.

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

Verify the result

You can execute the SHOW INDEX FROM test; statement to check whether the index is created.

Add attributes for index keys

• Add non-custom attributes for index keys

  Create a search index for all columns. If the c3 column is included in the index, set the following attributes for all index columns: type=text, analyzer=ik, indexed=true.

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

  Verify the result

  You can execute the SHOW INDEX FROM test; statement to check whether the index is created.

• Add custom attributes for index keys

  In this example, a sample table named test is created by executing the following statement:

  CREATE TABLE test (
    p1 VARCHAR NOT NULL,
    p2 INTEGER NOT NULL,
    c1 BIGINT,
    c2 DOUBLE,
    c3 VARCHAR,
    c4 TIMESTAMP,
    PRIMARY KEY(p1, p2)
  ) WITH (CONSISTENCY = 'strong', MUTABILITY='MUTABLE_LATEST');

  Create a search index for all columns. Configure the following attributes for the c3 column: type=text, analyzer=ik_max_word.

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

  Important

  • The mapping attribute is applicable only to LindormSearch (Compatible with Elasticsearch).

  • If you specify the mapping attribute, other attributes of the index key become invalid.

  Verify the result

  You can execute the SHOW INDEX FROM test; statement to check whether the index is created.

Configure the index status

Create a search index on the specified columns, specify index column attributes, and set the status of the search index to ACTIVE.

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

Verify the result

You can execute the SHOW INDEX FROM test; statement to check whether the index is created.

Configure custom index attributes

Create a search index and set the status of the search index to ACTIVE. Configure custom attributes to set the number of shards to 4, the compression algorithm to ZSTD, and the interval at which data is updated to 10 seconds.

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",
    "number_of_shards": 4
   }
}');

Verify the result

You can execute the SHOW INDEX FROM test; statement to check whether the index is created.

Configure time-based partitioning

Perform partitioning based on the time column c4. Partitioning is automatically performed every seven days from 30 days ago. By default, partitions are retained for 90 days.

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');

Verify the result

You can execute the SHOW INDEX FROM test; statement to check whether the index is created.