Lindorm:Manage search indexes

Prerequisites

• The search index feature is enabled for your Lindorm instance.

  Note

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

• Lindorm-cli is used to connect to LindormTable.

Limits

Prepare data

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

1. Execute the following statements 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. Execute the following statements to write four rows of data into 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. Execute 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

   • The above example specifies the value of numShards as 4, which means that the created index idx has 4 shards. If numShards is not specified, the default value of numShards is twice the number of search engine nodes. Before creating a search index for a production environment, we recommend that you plan the number of shards in advance to avoid excessive data volume in a single shard, which could affect system stability. For shard design instructions and index usage recommendations, see numShards parameter description.

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

   • When you create a search index for a table, Lindorm asynchronously builds the search index for the historical data in the table. If the table contains large amounts of historical data, a long period of time is required to build the search index. If you want to build a search index synchronously, append the SYNC keyword to the statement.

   • For information about errors that may occur when you create a search index and solutions to these errors, see FAQ.

2. Query the search index of 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 being built is in the BUILDING state. After a search index is built, the state of the index becomes ACTIVE. For more information about the returned results, see SHOW INDEX.

3. Use the search index to query data. For more information, see Query data in a wide table by 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

   In LindormTable 2.7.7 and earlier, before you delete a search index, you must execute the ALTER INDEX IF EXISTS idx ON search_table DISABLED; statement to disable the search index. After an index is disabled, it is in the DISABLED state. To enable the index again, you must execute the ALTER INDEX IF EXISTS idx ON search_table REBUILD; statement to rebuild it.

   DROP INDEX IF EXISTS idx ON search_table;

Use dynamic columns in search indexes

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 search index and specify dynamic columns.

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

   • If you have an existing search index, execute 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 dynamic column only in the 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 data in dynamic columns. You can query the password dynamic column when data in the column is stored in the 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 specify a wildcard column, you can dynamically write data to all columns that match the wildcard rule without the need to predefine specific columns. Wildcard columns are suitable for scenarios where data needs to be written to multiple columns whose names follow specific rules.

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

   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 specified as a tokenization column.

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

3. Write data to wildcard columns. The info_gender, info_email, and info_city columns match the wildcard column info_*, and the address_detail column matches the wildcard column address_*. You do not need to predefine these columns. During data writing, these columns automatically match the corresponding wildcard columns and inherit attributes such as data types 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 check whether the search index is used in a query, you can execute the following EXPLAIN statement: EXPLAIN SELECT * FROM search_table WHERE name='Wang' AND age > 18 AND info_city='Hangzhou' LIMIT 10;.

FAQ