Lindorm:Manage search indexes

Prerequisites

Before you begin, make sure you have:

• Enabled the search index feature

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

• A connection to LindormTable via Lindorm-cli

Limits

Search indexes have usage limits. Review the Limits page before using them in production.

Set up test data

The examples throughout this page use a table named search_table. Run the following statements to create the table and insert test data.

1. Create the database and 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. Insert four rows of test data.

   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. Verify the data.

   SELECT * FROM search_table LIMIT 10;

   Expected output:

   +---------+--------+-----+--------+-----------------------------+------------------+----------+
   | 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 |
   +---------+--------+-----+--------+-----------------------------+------------------+----------+

Create a search index

The goal for this example index:

• Search any column in the table

• Run tokenized (full-text) queries on the address column using the IK analyzer

• Run fuzzy queries on the email column

1. Create the 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);

   - numShards controls how many shards the index is split into. In this example, numShards=4 creates four shards. The default is twice the number of LindormSearch nodes. Plan your shard count before creating an index for production to avoid overloading individual shards. For guidance, see the numShards parameter description. - The address field is configured with type=text, analyzer=ik so that IK-analyzer tokenization applies to full-text queries on that column. - 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 when running CREATE INDEX, see the FAQ.

2. Check the index state.

   SHOW INDEX FROM search_table;

   Expected output:

   +----------------+--------------+------------+-------------+------------+---------------+------------------------------------+-----------+-------------------+
   |  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         |                   |
   +----------------+--------------+------------+-------------+------------+---------------+------------------------------------+-----------+-------------------+

   The INDEX_STATE field shows the current build status. All valid states are:

   State	Meaning
   BUILDING	The index is being built. Queries that rely on the index are not yet available.
   ACTIVE	The index is ready. All indexed columns are queryable.
   DISABLED	The index has been manually disabled and is not used for queries.

   Wait until the state is ACTIVE before running index-backed queries. For field definitions, see SHOW INDEX. To track the full build progress, log on to the LTS console, then choose Lindorm Search > Full Sync in the left navigation pane.

3. Query data through the index.

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

   Expected output:

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

   For more query examples, see Query wide table data using a search index. To confirm which engine a query uses, run an EXPLAIN statement.

Delete a search index

Run the following statement to delete the search index.

DROP INDEX IF EXISTS idx ON search_table;

ALTER INDEX IF EXISTS idx ON search_table DISABLED;

Use dynamic columns

Dynamic columns let you write data to columns that were not defined when the table was created. To make dynamic columns searchable, explicitly add them to a search index.

1. Enable dynamic columns on the table.

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

2. Add the dynamic column to a search index. In this example, password is a dynamic column that was not included in the original table schema.

   • When creating a new index, include the dynamic column in the column list:

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

   • To add the column to an existing index, use ALTER INDEX:

     ALTER INDEX idx ON search_table ADD COLUMNS(password);

3. Write data to the dynamic column. 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 the dynamic column. You can query the password column only when data is stored in HexString format.

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

   Expected output:

   +---------+------+-----+--------+----------------------------+------------------+----------+----------+
   | 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 column whose name contains a wildcard character (*). Any column written to the table whose name matches the wildcard pattern is automatically treated as part of that wildcard column, inheriting its data type and analyzer settings — no predefinition required. This is useful when you need to write data to many columns that follow a consistent naming pattern. For background, see Wildcard columns.

1. Create a table with wildcard columns. This example defines info_* and address_* as wildcard columns.

   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 the wildcard columns to a search index. The address_* wildcard column is configured with the IK analyzer for tokenized queries.

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

3. Write data. Columns like info_gender, info_email, and info_city match info_*; address_detail matches address_*. These columns are created automatically on write.

   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 the wildcard columns.

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

   Expected output:

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

   To verify that the query is routed to the search index, run: EXPLAIN SELECT * FROM search_table WHERE name='Wang' AND age > 18 AND info_city='Hangzhou' LIMIT 10;

Configure the refresh interval

Search indexes provide eventual consistency. Data written to the wide table becomes searchable only after the next refresh cycle completes.

LindormSearch refreshes index data at a fixed interval — 15 seconds by default. Reducing the interval shortens the lag between writes and searchability, but increases the load on LindormSearch. Adjust the setting based on your latency requirements and cluster capacity.

• If your workload requires data to be searchable immediately after a write, set the interval to 1s.

• If write throughput is high and strict freshness is not required, keep the default 15s or increase it.

Adjusting the refresh interval requires LindormTable version 2.8.6.1 or later. To check or upgrade your version, see the LindormTable version guide and minor version update.

The refresh interval is controlled by the SEARCH_INDEX_VISIBILITY_TRIGGER_INTERVAL property.

• Set the interval on an existing index:

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

• Set the interval when creating a new index:

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

• Check the current interval:

  SHOW INDEX FROM search_table;

FAQ