Lindorm:Manage search indexes

Prerequisites

• The search index feature is enabled for your Lindorm instance. For more information, see Enable the search index feature.

• Lindorm-cli is used to connect to LindormTable. For more information, see Use Lindorm-cli to connect to and use LindormTable.

Limits

Search indexes support only the following data types:

• Basic data types: BOOLEAN, BYTE, SHORT, INT, LONG, FLOAT, DOUBLE, STRING, CHAR, BINARY, and TIMESTAMP.

• JSON data type: JSON.

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

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. Execute the following statement to create a search index. For more information about how to create a search index, see CREATE SEARCH INDEX.

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

   Note

   • If a The Lindorm Search cluster address is null error is returned after you execute the statement, make sure that the search index feature is enabled for the Lindorm instance.

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

   • You can create only one search index for a table.

   • 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.

2. Execute the following statement to 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. If you no longer need a search index, you can delete the search index.

   1. Before you delete a search index, you must execute the following statement to disable the search index:

      ALTER INDEX IF EXISTS idx ON search_table DISABLED;

      Note

      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.

   2. Delete the index.

      DROP INDEX IF EXISTS idx ON search_table;

      For more information, see DROP INDEX.

Use dynamic columns in search indexes

1. Enable dynamic columns. For more information, see Enable dynamic columns.

2. Create a search index that includes the c2 and c3 dynamic columns.

   CREATE INDEX IF NOT EXISTS idx USING SEARCH ON search_table(p1,c1,c2,c3) WITH(indexState=ACTIVE);

3. After the search index is created, you can execute the following statement to check whether the dynamic columns are included in the search index:

   SHOW INDEX FROM search_table;

   The following result is returned:

   +--------------+--------------+------------+-------------+----------------+------------+---------------+------------------------------------+-----------+-------------------+
   | TABLE_SCHEMA |  DATA_TABLE  | INDEX_NAME | INDEX_STATE | INDEX_PROGRESS | INDEX_TYPE | INDEX_COVERED |            INDEX_COLUMN            | INDEX_TTL | INDEX_DESCRIPTION |
   +--------------+--------------+------------+-------------+----------------+------------+---------------+------------------------------------+-----------+-------------------+
   | test         | search_table | idx        | ACTIVE      | N/A            | SEARCH     | NA            | address,city,age,gender,name,email | 0         |                   |
   +--------------+--------------+------------+-------------+----------------+------------+---------------+------------------------------------+-----------+-------------------+

4. You can execute the following statement to add a dynamic column to an existing search index:

   ALTER INDEX idx ON search_table ADD COLUMNS(c4);

5. Write data to dynamic columns and query data in dynamic columns. For more information, see Write data to dynamic columns and Query data in dynamic columns.