The Tablestore SQL engine optimizes queries by selecting the best index for data reads and pushing computations down to the index layer.
Index selection
When a data table has Secondary index or Search index, the SQL engine selects the optimal read path before executing a query. The index can be selected automatically or specified manually.
Before using query optimization, ensure that you have created data table mappings. For more information, see DDL operations. To accelerate queries with a search index, also create the search index and its mapping.
Automatic selection rules
When you query a data table mapping, the SQL engine automatically selects an index based on the following priority:
|
Priority |
Target |
Condition |
|
1 |
Search index |
All columns in the WHERE, aggregate, and ORDER BY clauses are covered by the same search index. Example: |
|
2 |
Secondary index |
The secondary index matches more leading primary key columns in the WHERE clause than the data table (leftmost prefix matching) and covers all columns in the query. Example: The data table has primary keys a and b. A secondary index has primary keys c, a, and b. For |
|
3 |
CBO decision |
If neither rule applies, the SQL engine uses the internal CBO (Cost-Based Optimizer) to select the lower-cost option between the data table and secondary indexes. |
-
If the mapping is configured with strong consistency or requires exact aggregation results, the SQL engine does not automatically select a search index.
-
When both a secondary index and a search index cover all required columns, the SQL engine prefers the search index.
Manually specify an index
To use a specific index, manually specify it in one of the following ways:
use index syntax
Specify an index in the SELECT statement.
-- Force a full table scan (skip all indexes)
SELECT * FROM sampletable use index();
-- Use a search index
SELECT * FROM sampletable use index(sampletable_search_index);
-- Use a secondary index
SELECT * FROM sampletable use index(sampletable_secondary_index);If the specified index does not cover the required columns, the SQL engine automatically reads the missing data from the data table.
Index mapping table
Query the mapping table of a secondary index or search index directly.
-- Query through a search index mapping table (only columns in the index are queryable)
SELECT col_a, col_b FROM search_index_mapping WHERE col_a > 10;Queries on an index mapping table can only return columns that are included in the index.
Recommendations
|
Scenario |
Recommendation |
|
Aggregation, sorting, or full-text search |
Use automatic selection (default). Ensure that the search index covers all required columns. The SQL engine routes the query to the search index and pushes down operators automatically. |
|
Simple equality or range filters with strong consistency |
Use a secondary index. Secondary indexes support strongly consistent reads when you require real-time data. |
|
Automatic selection produces unexpected results |
Manually specify an index with |
|
Only columns in the index are needed |
Query the index mapping table directly to avoid reading from the data table. |
Computation pushdown
The SQL engine pushes supported operators down to the search index layer, reducing the volume of data the SQL engine must process.
Activation conditions
Computation pushdown requires the search index to cover all columns in the SQL statement, including SELECT, WHERE, ORDER BY, and GROUP BY columns. If any column is missing, the SQL engine falls back to a full table scan.
-- Table has columns a, b, c, d. Search index covers a, b, c.
-- d is not in the search index → full table scan, no pushdown
SELECT a, b, c, d FROM exampletable;
-- All columns are in the search index → reads through search index, pushdown supported
SELECT a, b, c FROM exampletable;Supported operators
|
Operator type |
Pushdown support |
Conditions |
|
Logical operators |
AND, OR |
NOT cannot be pushed down. |
|
Relational operators |
=, !=, <, <=, >, >=, BETWEEN...AND |
Only column-to-constant comparisons are supported. Column-to-column comparisons cannot be pushed down because search indexes build independent indexes per column and cannot resolve cross-column comparisons at the index layer. |
|
Aggregate functions |
MIN, MAX, COUNT, AVG, SUM, ANY_VALUE, COUNT(DISTINCT), GROUP BY |
Arguments must be column names, not expressions. COUNT(*) supports pushdown. |
|
Sorting and pagination |
ORDER BY col LIMIT n |
ORDER BY arguments must be column names. Expressions in ORDER BY cannot be pushed down. |
|
Vector functions |
VECTOR_QUERY_FLOAT32 |
All other expressions in the WHERE clause must also satisfy pushdown conditions. If any expression does not, the query cannot execute. |
Common anti-patterns
|
Anti-pattern |
Corrected pattern |
Reason |
|
|
Column a is BIGINT. Type mismatch triggers an implicit CAST. |
|
|
Aggregate argument is an expression. |
|
|
GROUP BY argument is an expression. |
|
No equivalent rewrite. Filter at the application layer. |
Column-to-column comparison. |
|
|
ORDER BY argument is an expression. |
SQL expressions and search index feature mapping
The following table maps SQL expressions to their equivalent search index query features, helping you migrate from the search index SDK to SQL.
|
SQL expression |
Example |
Search index feature |
|
No WHERE clause |
SELECT * FROM t |
|
|
= |
a = 1 |
|
|
>, >=, <, <= |
a > 1 |
|
|
IS NULL / IS NOT NULL |
a IS NULL |
|
|
AND / OR / NOT / != |
a = 1 AND b = 2 |
|
|
LIKE |
a LIKE "%s%" |
|
|
IN |
a IN (1,2,3) |
|
|
TEXT_MATCH |
TEXT_MATCH(a, "hello") |
|
|
TEXT_MATCH_PHRASE |
TEXT_MATCH_PHRASE(a, "hello world") |
|
|
ARRAY_EXTRACT |
ARRAY_EXTRACT(col) |
|
|
NESTED_QUERY |
NESTED_QUERY(expr) |
|
|
ORDER BY / LIMIT |
ORDER BY a LIMIT 10 |
|
|
Aggregate functions / GROUP BY |
SUM(col) / GROUP BY col |