How do I query data in a wide table by using a search index - Lindorm

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

You are connected to LindormTable by using Lindorm-cli. For more information, see Use Lindorm-cli to connect to and use LindormTable.
Test data is generated and a search index is created. For more information, see Manage search indexes.

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

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

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' ORDER BY age DESC LIMIT 1,10;

The following result is returned:

+---------+--------+-----+--------+--------------+------------------+------+
| user_id |  name  | age | gender |   address    |      email       | city |
+---------+--------+-----+--------+--------------+------------------+------+
| 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

You can 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' ORDER BY user_id;

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%' ORDER BY user_id;

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:
```
+----------+
| EXPR$0   |
+----------+
| 2        |
+----------+
```

Execute the following statement to query data and remove duplicate query results:

SELECT distinct(age) FROM search_table WHERE gender='M' ORDER BY user_id;

The following result is returned:

+---------------+
| DISTINCT(age) |
+---------------+
| 18            |
| 28            |
| 32            |
+---------------+

Execute the following statement to query data and group the query results:

SELECT city,count(*) AS cnt FROM search_table WHERE gender='M' GROUP BY city ORDER BY cnt DESC;

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:

SELECT name,age,city,address FROM search_table WHERE age>1 AND age<100 AND address='"Hangzhou"' ORDER BY user_id;

The following result is returned:

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