All Products
Search
Document Center

Lindorm:Manage search indexes

Last Updated:Mar 21, 2024

LindormTable supports search indexes that are applicable to multi-dimensional queries, fuzzy queries, and tokenized text queries. After you enable the search index feature for your Lindorm instance, you can use Lindorm SQL to create and manage search indexes. This topic describes how to use and manage search indexes.

Prerequisites

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

Limits

The search index feature does not support the TIME, TIMESTAMP, and spatial data types.

Data preparation

Before you use and manage search indexes, you must create a table and populate the table with test data.

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

  2. 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, constraint primary key (user_id asc));
  3. 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');
  4. (Optional) Execute the following statement to verify whether data is written to 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 test data. The index must meet the following requirements:

  • The index can be used to quickly search any column in the table.

  • The index can be used to perform queries based on tokenized text in the address column.

  • The index can be used to perform fuzzy queries based on the email column.

  1. Execute the following statement to create a search index. For more information about how to create a search index, see CREATE INDEX.

    CREATE SEARCH INDEX IF NOT EXISTS idx 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 synchronously 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 do not want to build a search index when you create the search index, you can append the ASYNC keyword to the statement, and then change the state of the search index to USABLE. For more information about how to change the state of a search index, see Modify the state of a search index.

  2. Execute the following statement to query the search index of the table:

    SHOW SEARCH 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 |
    +---------------+--------------+------------+-------------+------------+---------------+------------------------------------+-----------+
    | searchindex_db| search_table | idx        | BUILDING    | SEARCH     | NA            | address,city,age,gender,name,email | 0         |
    +---------------+--------------+------------+-------------+------------+---------------+------------------------------------+-----------+
  3. (Optional) Build a search index. If you use the ASYNC keyword in the statement to create a search index for a table, Lindorm does not build the search index for the historical data in the table. In this case, if you want to use the search index to query historical data in the table, execute the following statement to build the search index. After the search index is built, the state of the search index becomes ACTIVE. You can execute the following statement to query the state of the search index: SHOW SEARCH INDEX FROM search_table;.

    ALTER SEARCH INDEX IF EXISTS idx ON search_table REBUILD;
    Note

    For more information about the syntax used to build a search index, see ALTER INDEX.

  4. 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 |
    +---------+--------+-----+--------+--------------+------------------+------+
  5. If you no longer use a search index, you can execute the following statement to delete the search index. For more information about the syntax used to delete a search index, see DROP INDEX.

    ALTER SEARCH INDEX IF EXISTS idx ON search_table DISABLED;
    DROP SEARCH INDEX IF EXISTS idx ON search_table;

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 SEARCH INDEX IF NOT EXISTS idx 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 SEARCH 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 SEARCH 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.