All Products
Search
Document Center

Lindorm:Query data in a wide table by using a search index

Last Updated:Sep 21, 2023

This topic describes how to use a search index to query data in a Lindorm wide table in multidimensional queries, paged queries, queries with results sorted, tokenized text queries, fuzzy queries, and aggregate queries.

Prerequisites

Run a multidimensional query

Execute the following statements to run multidimensional queries:

  • Example 1:

    SELECT * FROM search_table WHERE gender='M' AND city='Hangzhou' OR city='Beijing';

    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 |
    +---------+--------+-----+--------+--------------+------------------+------+
  • Example 2:

    SELECT * FROM search_table WHERE age > 30 AND city ! ='Hangzhou ';

    The following result is returned:

    +---------+--------+-----+--------+--------------+------------------+------+
    | user_id |  name  | age | gender |   address    |      email       | city |
    +---------+--------+-----+--------+--------------+------------------+------+
    | 28 | Chen | 36 | F | Nanshan District, Shenzhen | a***@example.net | Shenzhen |
    +---------+--------+-----+--------+--------------+------------------+------+

Run a paged query and sort query results

  • A search index allows you to use limit or offset to perform data pagination. Execute the following statement to run a paged query:

    SELECT * FROM search_table  WHERE gender='M' LIMIT 1,10;

    The following result is returned:

    +---------+--------+-----+--------+--------------+------------------+------+
    | user_id |  name  | age | gender |   address    |      email       | city |
    +---------+--------+-----+--------+--------------+------------------+------+
    | 6 | Li | 32 | M | Yuhang District, Hangzhou | a***@example.net | Hangzhou |
    | 20 | Wang | 28 | M | Binjiang District, Hangzhou | a***@example.net | Hangzhou |
    +---------+--------+-----+--------+--------------+------------------+------+
  • A search index allows you to sort data based on each of the columns in your table. Execute the following statement to sort the output:

    SELECT * FROM search_table  WHERE gender='M' ORDER BY age DESC;

    The following result is returned:

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

Run a tokenized text query

Use = to query tokenized text in a column. In the following example, the address column stores the tokenized text. Execute the following statement to query data rows that include values that are larger than 10 and equal to or less than 50 in the age column and that include Yuhang District in the address column:

SELECT * FROM search_table WHERE age > 10 AND age <= 50 AND address='Yuhang District';

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

Run a fuzzy query

Execute the following statement to query data that includes example in the email column:

SELECT * FROM search_table WHERE email LIKE '%example%';

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

The performance of fuzzy queries is limited due to the amount of data in tables. Therefore, we recommend that you run tokenized text queries instead of fuzzy queries.

Run an aggregate query

Search indexes support common aggregate functions such as COUNT, SUM, AVG, MIN, and MAX. Search indexes also support the DISTINCT and GROUP BY clauses.

  • Execute the following statement to query the number of data rows that include Hangzhou in the city column:

    SELECT COUNT(*) FROM search_table WHERE city='Hangzhou';

    The following result is returned:

    +----------+
    | COUNT(*) |
    +----------+
    | 2        |
    +----------+
  • Execute the following statement to query data and remove duplicate query results:

    SELECTdistinct(age)FROMsearch_tableWHEREgender='M';

    The following result is returned:

    +---------------+
    | DISTINCT(age) |
    +---------------+
    | 18            |
    | 28            |
    | 32            |
    +---------------+
  • Execute the following statement to query data and group the query results:

    SELECTcity,count(*)ascntFROMsearch_tableWHEREgender='M'GROUPBYcityORDERBYcntDESC;

    The following result is returned:

    +------+-----+
    | city | cnt |
    +------+-----+
    | Hangzhou | 2 |
    | Beijing | 1 |
    +------+-----+
Note

If you do not specify a WHERE condition in an aggregate query, the corresponding search index cannot be hit. In this case, you can add force index(search_index_name) before the WHERE keyword to force the query to hit a specified index. For more information, see SELECT.

Run a phrase-based query

Execute the following statement to run a query with phrases specified as a condition:

SELECTname,age,city,addressFROMsearch_tableWHEREage>1ANDage<100ANDaddress='"Hangzhou"';

The following result is returned:

+--------+-----+------+--------------+
|  name  | age | city |   address    |
+--------+-----+------+--------------+
| Li | 32 | Hangzhou | Yuhang District, Hangzhou |
| Wang | 28 | Hangzhou | Binjiang District, Hangzhou |
+--------+-----+------+--------------+