Lindorm:CREATE INDEX

Asynchronously build an index

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

Result Verification

Run SHOW INDEX FROM test to check whether the index is created.

Create a composite index

Create a composite index on columns c1, c2, and c3 synchronously.

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

Verifying the results

Run SHOW INDEX FROM test to check whether the index is created.

Create a spatio-temporal secondary index

The following example shows how to create a spatio-temporal secondary index. For more information, see Spatio-temporal indexes.

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

Include all columns

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

Verifying the results

Run 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

Run 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

Run SHOW INDEX FROM test to check whether the index is created.

Specify the start and end keys for the index table

When you create a secondary index, you can specify the start and end keys and the number of pre-partitions for the index table. This divides 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 results

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

Verifying the result

Run 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 the 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;

Verifying the results

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

Verifying the result

Run SHOW INDEX FROM test to check whether the index is created.

Asynchronously build an index

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

Verifying the results

Run 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 no specific column properties are specified, the default values are used.

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

Verify the result

Run 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. If the index includes column c3, set its properties 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

  Run SHOW INDEX FROM test to check whether the index is created.

  Note

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

• Add custom index key properties

  Assume that 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, where the index column c3 is specified as type text and the tokenizer is 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 versions.

  • If the mapping parameter is used, all other parameters for this index key are invalid.

  Verifying the result

  Run SHOW INDEX FROM test to check whether the index is created.

  Note

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

Set the index state

Specify columns and 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);

Verifying the results

Run 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

Run SHOW INDEX FROM test to check whether the index is created.

Set time partitions

Partition by the time column c4, starting from 30 days ago, with automatic partitioning every 7 days. By default, partition data for 90 days is retained.

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

Verification

Run SHOW INDEX FROM test to check whether the index is created.

Set storage for raw data

Search indexes are used only for data filtering and do not store the raw data of index columns by default. To directly access the search engine when you use a search index, you can set the index to store all or part of the raw data of the index columns.

• Set storage for the raw data of all index columns

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

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

  Verify the results

  Run SHOW INDEX FROM test to check whether the index is created.

  Note

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

• Set storage for the raw data of some index columns

  Create a search index that includes 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"]
  }  
  ');

  Verifying the result

  Run SHOW INDEX FROM test to check whether the index is created.

  Note

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