Lindorm:CREATE INDEX

Build an index asynchronously

If you do not specify a building mode, the index is built asynchronously by default.

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

Verify the result

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

Create a composite index

Synchronously create a composite index for columns c1, c2, and c3.

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

Verify the result

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

Include all columns

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

Verify the result

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

Include all dynamic columns

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

Verify the result

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

Set the number of pre-partitions for the index table

Set the number of pre-partitions for the index table to 32.

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

Verify the result

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

Specify the start and end keys for the index table

You can specify the start and end keys and the number of pre-partitions for the index table when you create a secondary index. This example splits the index table into 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

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

Create a Z-ORDER secondary index

Create a Z-ORDER secondary index for the polygon data type column c5.

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

Verify the result

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

Create a grid secondary index

1. Create a grid secondary index for the polygon type column c5. Currently, only asynchronous creation mode is supported.

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

2. Build the grid secondary index.

   BUILD INDEX s2_idx ON test;

Verify the result

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

Convert the data type of a specified column

Convert the data type of column c3 to INTEGER and then create a secondary index.

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

Verify the result

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

Build an index asynchronously

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

Verify the result

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

Create an index for all columns

Create a search index for all columns. If you do not specify column properties, the default values are used.

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

Verify the result

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

Add index key properties

• Add non-custom index key properties

  Create a search index for all columns. For the index column c3, the properties are set to type=text, analyzer=ik, and 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 SHOW INDEX FROM test; to check whether the index is created.

  Note

  You can also view the index building progress in the LTS console. For more information, see View the progress of a full build for a search index.

• Add custom index key properties

  Assume the table schema is as follows:

  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. For index column c3, specify the type as text and the tokenizer as ik_max_word.

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

  Important

  • The mapping parameter applies only to Elasticsearch-compatible search engines.

  • If you use the mapping parameter, all other parameters for this index key become invalid.

  Verify the result

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

  Note

  You can also view the index building progress in the LTS console. For more information, see View the progress of a full build for a search index.

Set the index state

Specify columns, specify some column properties, and set the search index state 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 SHOW INDEX FROM test; to check whether the index is created.

Set custom index properties

Create a search index, set the search index state to ACTIVE, and use custom settings to specify the index compression method as ZSTD and the refresh interval as 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"
   }
}');

Verify the result

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

Set a time partition

Partition the index by the time column c4. This example starts partitioning from 30 days ago, creates a new partition every 7 days, and retains partition data for the default period of 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 SHOW INDEX FROM test; to check whether the index is created.

Set raw data storage

By default, a search index is used only for data filtering and does not store the raw data of index columns. To retrieve raw data directly from the search engine without accessing the primary table, you can configure the index to store all or part of the raw data of the index columns.

• Store the raw data of all index columns

  You can create a search index for the c1, c2, c3, and c4 columns, and store the raw data of the c1, c2, c3, and c4 columns.

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

  Verify the result

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

  Note

  You can also view the index building progress in the LTS console. For more information, see View the progress of a full build for a search index.

• Store the raw data of some index columns

  Create a search index that contains columns c1, c2, c3, and c4, and store the raw data of columns c2, c3, and c4.

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

  Verify the result

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

  Note

  You can also view the index building progress in the LTS console. For more information, see View the progress of a full build for a search index.