Lindorm:Manage search indexes

Prerequisites

• You have enabled the search index.

  Note

  The search index feature depends on LindormSearch and Lindorm Tunnel Service (LTS). To enable this feature, you must also enable LindormSearch and LTS.

• Connect to LindormTable using Lindorm-cli.

Limits

Search indexes have usage limits. To avoid affecting your production workloads, review the Limits before you use them.

Data Preparation

Before you use search indexes, create a table and write test data to it.

1. Run the following statement to create a table named search_table.

   CREATE DATABASE searchindex_db;
   USE searchindex_db;
   CREATE TABLE IF NOT EXISTS search_table (user_id BIGINT, name VARCHAR, age SMALLINT, gender VARCHAR, address VARCHAR, email VARCHAR, city VARCHAR, PRIMARY KEY (user_id));

2. Run the following statement to write four rows of data to the table.

   UPSERT INTO search_table (user_id,name,age,gender,address,email,city) VALUES (1, 'Zhang', 18, 'M', 'Chaoyang District, Beijing', 'a***@example.net', 'Beijing');
   UPSERT INTO search_table (user_id,name,age,gender,address,email,city) VALUES (6, 'Li', 32, 'M', 'Yuhang District, Hangzhou', 'a***@example.net', 'Hangzhou');
   UPSERT INTO search_table (user_id,name,age,gender,address,email,city) VALUES (20, 'Wang', 28, 'M', 'Binjiang District, Hangzhou', 'a***@example.net', 'Hangzhou');
   UPSERT INTO search_table (user_id,name,age,gender,address,email,city) VALUES (28, 'Chen', 36, 'F', 'Nanshan District, Shenzhen', 'a***@example.net', 'Shenzhen');

3. Run the following statement to query data in the table.

   SELECT * FROM search_table LIMIT 10;

   The following result is returned:

   +---------+--------+-----+--------+--------------+------------------+------+
   | user_id |  name  | age | gender |   address    |      email       | city |
   +---------+--------+-----+--------+--------------+------------------+------+
   | 1       | Zhang  | 18  | M      | Chaoyang District, Beijing | a***@example.net  | Beijing |
   | 6       | Li     | 32  | M      | Yuhang District, Hangzhou  | a***@example.net  | Hangzhou |
   | 20      | Wang   | 28  | M      | Binjiang District, Hangzhou | a***@example.net  | Hangzhou |
   | 28      | Chen   | 36  | F      | Nanshan District, Shenzhen | a***@example.net  | Shenzhen |
   +---------+--------+-----+--------+--------------+------------------+------+

Manage search indexes

Create a search index based on the test data to meet the following requirements:

1. Create a search index.

   CREATE INDEX IF NOT EXISTS idx USING SEARCH ON search_table (
     name,
     age,
     gender,
     address(type=text, analyzer=ik),
     email,
     city
   ) WITH (numShards=4);

   Note

   • In this example, numShards is set to 4. This means the index idx has 4 shards. If you do not specify numShards, its default value is twice the number of LindormSearch nodes. Before creating a search index for production, plan your shard count in advance. This avoids overloading individual shards and helps maintain system stability. For more information about shard design and indexing best practices, see numShards parameter description.

   • In the statement, the address field is tokenized using the IK analyzer.

   • By default, Lindorm builds the search index asynchronously in the background. The more historical data the table contains, the longer the build takes. To build the index synchronously, append the SYNC keyword to the statement.

   • For common errors and solutions when running the CREATE INDEX statement, see the FAQ.

2. Query the search index information for the table.

   SHOW INDEX FROM search_table;

   The following result is returned:

   +---------------+--------------+------------+-------------+------------+---------------+------------------------------------+-----------+-------------------+
   | TABLE_SCHEMA  |  DATA_TABLE  | INDEX_NAME | INDEX_STATE | INDEX_TYPE | INDEX_COVERED |            INDEX_COLUMN            | INDEX_TTL | INDEX_DESCRIPTION |
   +---------------+--------------+------------+-------------+------------+---------------+------------------------------------+-----------+-------------------+
   | searchindex_db| search_table | idx        | BUILDING    | SEARCH     | NA            | address,city,age,gender,name,email | 0         |                   |
   +---------------+--------------+------------+-------------+------------+---------------+------------------------------------+-----------+-------------------+

   Note

   • A search index that is still building shows BUILDING as its state. After the build completes, the state changes to ACTIVE. For details about the returned fields, see SHOW INDEX.

   • You can check the index build progress in the LTS console. For instructions, see How to view the full build progress of a search index.

3. Query data. For more query examples, see Query wide table data using a search index.

   SELECT * FROM search_table WHERE name='Wang' AND age > 18 AND city='Hangzhou';

   The following result is returned:

   +---------+--------+-----+--------+--------------+------------------+------+
   | user_id |  name  | age | gender |   address    |      email       | city |
   +---------+--------+-----+--------+--------------+------------------+------+
   | 20      | Wang   | 28  | M      | Binjiang District, Hangzhou | a***@example.net | Hangzhou |
   +---------+--------+-----+--------+--------------+------------------+------+

4. Delete the search index.

   Important

   For LindormTable versions earlier than 2.7.7, disable the index first by running ALTER INDEX IF EXISTS idx ON search_table DISABLED;. After disabling, the index state becomes DISABLED. To re-enable it, run ALTER INDEX IF EXISTS idx ON search_table REBUILD;.

   DROP INDEX IF EXISTS idx ON search_table;

Use dynamic columns

1. Enable dynamic columns.

   ALTER TABLE search_table SET 'DYNAMIC_COLUMNS' = 'true';

2. Add dynamic columns. Dynamic columns need to be explicitly added to the search index. In this example, the dynamic column is the password column, which is not specified when the table is created.

   • Create a new search index and include dynamic columns.

     CREATE INDEX idx USING SEARCH ON search_table(user_id,name,age,gender,password);

   • If you already have a search index, use the ALTER INDEX statement to add dynamic columns.

     ALTER INDEX idx ON search_table ADD COLUMNS(password);

3. Write data to dynamic columns. You can write data to the password column only in HexString format.

   UPSERT INTO search_table (user_id,name,age,gender,address,email,city,password) VALUES (30, 'Wang', 38, 'F', 'Nanshan District, Shenzhen', 'a***@example.net', 'Shenzhen', 'ef0011');

4. Query dynamic columns. You can query the password column only when data is stored in HexString format.

   SELECT * FROM search_table WHERE password='ef0011' LIMIT 1;

   The following result is returned:

   +---------+--------+-----+--------+--------------+------------------+------+----------+
   | user_id |  name  | age | gender |   address    |      email       | city | password |
   +---------+--------+-----+--------+--------------+------------------+------+----------+
   | 30      | Wang   | 38  | F      | Nanshan District, Shenzhen | a***@example.net | Shenzhen | 0xef0011 |
   +---------+--------+-----+--------+--------------+------------------+------+----------+

Use wildcard columns

A wildcard column is a special column whose name contains a wildcard character. After you define a wildcard column, you can write data to any column that matches the wildcard rule without predefining each column. Wildcard columns are useful when you need to write data to many columns with names that follow a specific pattern. For more information, see Wildcard columns.

1. Create a wide table and define wildcard columns. In this example, the wildcard columns info_* and address_* are defined.

   CREATE TABLE search_table (user_id BIGINT, name VARCHAR, age SMALLINT, `info_*` VARCHAR, `address_*` VARCHAR, PRIMARY KEY (user_id)) WITH (wildcard_column='info_*,address_*');

2. Add wildcard columns to the search index. The wildcard column address_* is configured as a tokenized column.

    CREATE INDEX idx USING SEARCH ON search_table (name,age,`info_*`,`address_*`(type=text, analyzer=ik));

3. Write data to wildcard columns. The columns info_gender, info_email, and info_city match the wildcard column info_*. The column address_detail matches the wildcard column address_*. You do not need to predefine these columns. When you write data, they automatically match the corresponding wildcard column and inherit attributes such as data type and tokenization settings.

   UPSERT INTO search_table (user_id,name,age,info_gender,address_detail,info_email,info_city) VALUES (1, 'Zhang', 18, 'M', 'Chaoyang District, Beijing', 'a***@example.net', 'Beijing');
   UPSERT INTO search_table (user_id,name,age,info_gender,address_detail,info_email,info_city) VALUES (6, 'Li', 32, 'M', 'Yuhang District, Hangzhou', 'a***@example.net', 'Hangzhou');
   UPSERT INTO search_table (user_id,name,age,info_gender,address_detail,info_email,info_city) VALUES (20, 'Wang', 28, 'M', 'Binjiang District, Hangzhou', 'a***@example.net', 'Hangzhou');
   UPSERT INTO search_table (user_id,name,age,info_gender,address_detail,info_email,info_city) VALUES (28, 'Chen', 36, 'F', 'Nanshan District, Shenzhen', 'a***@example.net', 'Shenzhen');

4. Query wildcard columns.

   SELECT * FROM search_table WHERE name='Wang' AND age > 18 AND info_city='Hangzhou' LIMIT 10;

   The following result is returned:

   +---------+--------+-----+----------------+-----------+------------------+-------------+
   | user_id | name   | age | address_detail | info_city | info_email       | info_gender |
   +---------+--------+-----+----------------+-----------+------------------+-------------+
   | 20      | Wang   | 28  | Binjiang District, Hangzhou | Hangzhou | a***@example.net | M           |
   +---------+--------+-----+----------------+-----------+------------------+-------------+

   Note

   To verify whether the query uses the search index, run the following EXPLAIN statement: EXPLAIN SELECT * FROM search_table WHERE name='Wang' AND age > 18 AND info_city='Hangzhou' LIMIT 10;.

Configure the data refresh interval

Search indexes provide eventual consistency. Data written to a wide table becomes searchable through the search index only after one refresh cycle.

LindormSearch performs data refreshes at fixed intervals in the background. By default, the refresh interval is 15 seconds. Reducing this interval shortens the time between data writes and when the data becomes searchable. However, it also increases the load on LindormSearch. Adjust this setting based on your resource usage and business needs.

The data refresh interval is controlled by the index property SEARCH_INDEX_VISIBILITY_TRIGGER_INTERVAL.

For an existing search index, use ALTER INDEX to adjust the property:

ALTER INDEX idx ON search_table SET SEARCH_INDEX_VISIBILITY_TRIGGER_INTERVAL='1s';

When creating a new search index, use the WITH clause to set the property:

CREATE INDEX idx USING SEARCH ON search_table (name) WITH (SEARCH_INDEX_VISIBILITY_TRIGGER_INTERVAL='1s');

You can check the current data refresh interval using SHOW INDEX:

SHOW INDEX FROM search_table;

FAQ