A search index lets you run multidimensional filters, sorts, paginated queries, full-text searches, and aggregations against a LindormTable wide table without full-table scans. This guide shows the SQL syntax for each query type with working examples.
Prerequisites
Before you begin, make sure you have:
Connected to LindormTable using Lindorm-cli. See Connect to and use LindormTable with Lindorm-cli
Prepared test data and created a search index. See Manage search indexes
Multidimensional queries
Use AND, OR, and comparison operators to filter on multiple columns simultaneously.
Example 1: Return all male users in Hangzhou or Beijing.
SELECT * FROM search_table WHERE gender='M' AND city='Hangzhou' OR city='Beijing';Result:
+---------+--------+-----+--------+-----------------------------+------------------+----------+
| user_id | name | age | gender | address | email | city |
+---------+--------+-----+--------+-----------------------------+------------------+----------+
| 6 | Li | 32 | M | Yuhang District, Hangzhou | a***@example.net | Hangzhou |
| 1 | Zhang | 18 | M | Chaoyang District, Beijing | a***@example.net | Beijing |
| 20 | Wang | 28 | M | Binjiang District, Hangzhou | a***@example.net | Hangzhou |
+---------+--------+-----+--------+-----------------------------+------------------+----------+Example 2: Return users older than 30 who are not in Hangzhou.
SELECT * FROM search_table WHERE age > 30 AND city != 'Hangzhou';Result:
+---------+------+-----+--------+----------------------------+------------------+----------+
| user_id | name | age | gender | address | email | city |
+---------+------+-----+--------+----------------------------+------------------+----------+
| 28 | Chen | 36 | F | Nanshan District, Shenzhen | a***@example.net | Shenzhen |
+---------+------+-----+--------+----------------------------+------------------+----------+Sorting and pagination
A search index supports sorting on any column. Combine ORDER BY with LIMIT and OFFSET for paginated results.
Sort: Return male users sorted by age in descending order.
SELECT * FROM search_table WHERE gender='M' ORDER BY age DESC;Result:
+---------+-------+-----+--------+-----------------------------+------------------+----------+
| 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 |
+---------+-------+-----+--------+-----------------------------+------------------+----------+Paginate: Skip the first result and return up to 10 records (LIMIT offset, count).
SELECT * FROM search_table WHERE gender='M' ORDER BY age DESC LIMIT 1,10;Result:
+---------+-------+-----+--------+-----------------------------+------------------+----------+
| 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 |
+---------+-------+-----+--------+-----------------------------+------------------+----------+Fuzzy queries
Use the LIKE operator with % wildcards to match partial strings.
Example: Return all users whose email contains example.
SELECT * FROM search_table WHERE email LIKE '%example%';Result:
+---------+-------+-----+--------+-----------------------------+------------------+----------+
| 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 |
+---------+-------+-----+--------+-----------------------------+------------------+----------+Fuzzy query performance degrades as table size grows. For tokenized columns, use tokenized text queries instead.
Aggregate queries
Search indexes support COUNT, SUM, AVG, MIN, MAX, DISTINCT, and GROUP BY.
Count: Count rows where city is Hangzhou.
SELECT COUNT(*) FROM search_table WHERE city='Hangzhou';Result:
+--------+
| EXPR$0 |
+--------+
| 2 |
+--------+Deduplicate: Return distinct ages for male users.
SELECT distinct(age) FROM search_table WHERE gender='M';Result:
+---------------+
| DISTINCT(age) |
+---------------+
| 18 |
| 28 |
| 32 |
+---------------+Group: Count male users per city, sorted by count.
SELECT city, count(*) AS cnt FROM search_table WHERE gender='M' GROUP BY city ORDER BY cnt DESC;Result:
+----------+-----+
| city | cnt |
+----------+-----+
| Hangzhou | 2 |
| Beijing | 1 |
+----------+-----+Without a WHERE clause, an aggregate query does not use the search index. To force index usage, add the _l_force_index_ HINT parameter before the WHERE keyword. See Parameters of hintOptions and Example 4: Specify _l_force_index_ to forcibly use an index.
Tokenized text queries
For columns configured with a tokenizer, use the MATCH ... AGAINST syntax to search within token boundaries. This is faster and more accurate than fuzzy queries on large datasets.
The MATCH function requires LindormTable 2.7.2 or later. To check or upgrade your version, see Release notes for LindormTable and Minor version update.
All examples use the MATCH function on the address column, which is configured as a tokenized column.
Token match (unquoted): Match any record where the address token list contains Hangzhou, with age in the range (10, 30]. Passing the search term without quotes matches individual tokens.
SELECT * FROM search_table WHERE age > 10 AND age <= 30 AND MATCH (address) AGAINST ('Hangzhou');Result:
+---------+------+-----+--------+-----------------------------+------------------+----------+
| user_id | name | age | gender | address | email | city |
+---------+------+-----+--------+-----------------------------+------------------+----------+
| 20 | Wang | 28 | M | Binjiang District, Hangzhou | a***@example.net | Hangzhou |
+---------+------+-----+--------+-----------------------------+------------------+----------+Phrase match (quoted): Match records where address contains the exact phrase Yuhang District, Hangzhou, with age in the range (30, 35]. Wrapping the search term in double quotes inside the AGAINST clause switches from token matching to exact phrase matching.
SELECT * FROM search_table WHERE age > 30 AND age <= 35 AND MATCH (address) AGAINST ('"Yuhang District, Hangzhou"');Result:
+---------+------+-----+--------+---------------------------+------------------+----------+
| user_id | name | age | gender | address | email | city |
+---------+------+-----+--------+---------------------------+------------------+----------+
| 6 | Li | 32 | M | Yuhang District, Hangzhou | a***@example.net | Hangzhou |
+---------+------+-----+--------+---------------------------+------------------+----------+Equality-based tokenized queries (deprecated)
Equality-based tokenized text queries are deprecated. Starting from LindormTable 2.8.2.3, new search indexes no longer support them—existing indexes are unaffected. Upgrade to LindormTable 2.7.2 or later and use the MATCH function instead. See Release notes for LindormTable and Minor version update.
In versions earlier than 2.7.2, use the equality operator = to query tokenized columns:
SELECT * FROM search_table WHERE age > 10 AND age <= 30 AND address = 'Hangzhou';Result:
+---------+------+-----+--------+-----------------------------+------------------+----------+
| user_id | name | age | gender | address | email | city |
+---------+------+-----+--------+-----------------------------+------------------+----------+
| 20 | Wang | 28 | M | Binjiang District, Hangzhou | a***@example.net | Hangzhou |
+---------+------+-----+--------+-----------------------------+------------------+----------+Tokenized text subfield
This feature requires LindormTable 2.8.0 or later and LindormSearch compatible with Elasticsearch. If your version does not meet these requirements, update the minor version.
Background
When a VARCHAR column is set to the tokenized type (type=text), the analyzer splits stored values into tokens at index time. For example, with the IK analyzer, Binjiang District, Hangzhou is split into two tokens: Hangzhou and Binjiang District.
Because the original string is replaced by its tokens, LIKE fuzzy queries on the raw string may return no results:
-- Returns no results: the IK analyzer splits 'Binjiang District, Hangzhou' into
-- [Hangzhou] and [Binjiang District], so '%Binjiang District%' has no raw string to match.
SELECT * FROM search_table WHERE city LIKE '%Binjiang District%' LIMIT 10;The tokenized text subfield feature solves this by storing both the full original string (for exact and fuzzy matching) and its tokens (for MATCH queries) in the same index column.
Enable the feature
Set the column attribute to type=string,textSubField=true,analyzer=ik when creating the index. Choose the analyzer based on your use case. See analyzer type.
CREATE INDEX idx USING SEARCH ON search_table(user_id, address(type=string, textSubField=true, analyzer=ik));If a search index already exists, use Single-table multiple search indexes (public preview) to create a new index with the subfield enabled, then switch queries to the new index.
Query methods
With the tokenized text subfield enabled, the address column supports three query types simultaneously.
Equivalent queries use full-string matching against the original stored value.
-- No results: no record has address exactly equal to 'Hangzhou'
SELECT * FROM search_table WHERE address='Hangzhou' LIMIT 10;-- Returns user_id 20: the full string 'Binjiang District, Hangzhou' matches exactly
SELECT * FROM search_table WHERE address='Binjiang District, Hangzhou' LIMIT 10;Result:
+---------+------+-----+--------+-----------------------------+------------------+----------+
| user_id | name | age | gender | address | email | city |
+---------+------+-----+--------+-----------------------------+------------------+----------+
| 20 | Wang | 28 | M | Binjiang District, Hangzhou | a***@example.net | Hangzhou |
+---------+------+-----+--------+-----------------------------+------------------+----------+Fuzzy queries use LIKE against the original stored value.
SELECT * FROM search_table WHERE address LIKE '%Binjiang%' LIMIT 10;Result:
+---------+------+-----+--------+-----------------------------+------------------+----------+
| user_id | name | age | gender | address | email | city |
+---------+------+-----+--------+-----------------------------+------------------+----------+
| 20 | Wang | 28 | M | Binjiang District, Hangzhou | a***@example.net | Hangzhou |
+---------+------+-----+--------+-----------------------------+------------------+----------+Tokenized text queries use the MATCH function against the token index.
SELECT * FROM search_table WHERE MATCH (address) AGAINST ('Hangzhou') LIMIT 10;Result:
+---------+------+-----+--------+-----------------------------+------------------+----------+
| 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 |
+---------+------+-----+--------+-----------------------------+------------------+----------+The MATCH query returns both rows because both addresses contain the token Hangzhou, even though their full strings differ.