Use a secondary index or search index to accelerate data queries - Tablestore

Tablestore can store large volumes of structured data and supports various types of index schemas for accelerated query and analytics in different scenarios. This topic describes how to select an index that is used to query data by executing SQL statements when a secondary index and a search index are created for a data table.

Note

For more information about secondary indexes and search indexes, see Secondary index and Search index.

Use a mapping table created for a data table

If a secondary index and a search index are created for a data table, an index selection policy is required to specify an index when you execute SQL statements to query data. An index selection policy can be automatic selection or manual selection.

Important

Make sure that a mapping table is created for the data table. For more information, see Create a mapping table for a table.

Automatic selection

When you use the mapping table that is created for the data table to query data, Tablestore automatically selects the data table, secondary index, or search index for data query.

Important

If you specify that the query results must be in strong consistency mode or the query performance cannot be improved by compromising the accuracy of aggregate operations when you create a mapping table for the data table whose data you want to query, Tablestore does not automatically select the search index for data query.
If the secondary index and search index contain the columns that are specified in the SQL statements, Tablestore automatically selects the search index for data query.

The automatic selection policy is executed based on the following procedure:

Automatically select a search index: If all columns that are involved in the filter, aggregate, and sorting operations in the WHERE clause are contained in a search index, Tablestore automatically selects the search index for data query.
For example, the SELECT A,B,C FROM sampletable WHERE A=XXX and D = YY; statement is executed to query data. If the A, B, C, and D columns are contained in a search index that is created for the data table, Tablestore automatically selects the search index for data query.
If SQL statements that combine GROUP BY clauses and aggregate functions match the aggregation capability of the Search API operation of a search index, Tablestore also identifies operators and pushes the operators down to the search index. For more information about operator pushdown, see Computing pushdown.
Automatically select a secondary index: If a secondary index contains more primary key columns that are specified in the WHERE clause than the data table and the secondary index contains all columns that are involved in the SQL statement, Tablestore automatically selects the secondary index for data query. The primary key conditions in the WHERE clause is subject to the leftmost matching principle.
For example, the primary key columns of a data table are a and b, and the primary key columns of a secondary index that is created for the data table are c, a, and b. If the query condition is c = 1 and a > 1, the secondary index contains two primary key columns that are specified in the condition and the data table contains one primary key column that is specified in the condition. In this case, Tablestore automatically selects the secondary index for data query.
Automatically select a data table or secondary index: Tablestore selects a data table or secondary index based on the cost-based optimization (CBO) logic of the SQL engine and the pattern of the SQL statement for data query.

Manual selection

You can use the use index hint to explicitly specify an index for data query or explicitly specify that a mapping table that is created for an index is used for data query.

In this example, a data table named sampletable, a search index named sampletable_search_index, and a secondary index named sampletable_secondary_index created for the data table are used to describe the operations that are required in the manual selection policy.

Explicitly specify the data table that you want to access
The following sample SQL statement provides an example on how to explicitly specify the data table that you want to access:
```
SELECT * FROM sampletable use index();
```
Explicitly specify the search index or secondary index that you want to access
Note
If the index does not contain the columns that are involved in the SQL query, the SQL engine automatically queries data from the data table to obtain the required data.
The following sample SQL statement provides an example on how to explicitly specify the index that you want to access:
```
SELECT * FROM sampletable use index(sampletable_search_index); --Explicitly specify the search index that you want to access.
SELECT * FROM sampletable use index(sampletable_secondary_index); --Explicitly specify the secondary index that you want to access.
```

Use a mapping table that is created for a secondary index

When you use a secondary index to query data, you can perform the following steps to use a mapping table that is created for the secondary index for data query.

Note

When you use a mapping table that is created for a secondary index to query data, you can only query the data columns that are contained in the secondary index.

Execute the CREATE TABLE statement to create a mapping table for the secondary index. For more information, see Create a mapping table for a table.
Execute the SELECT statement to use the mapping table that is created for the secondary index to query data. For more information, see Query data.

Use a mapping table that is created for a search index

When you use a search index to query data, you can perform the following steps to use a mapping table that is created for the search index for data query.

Note

When you use a mapping table that is created for a search index to query data, you can only query the data columns that are contained in the search index.

Execute the CREATE TABLE statement to create a mapping table for the search index. For more information, see Create a mapping table for a table.
Execute the SELECT statement to use the mapping table that is created for the search index to query data. For more information, see Query data.

Appendix: mappings between features of search indexes and SQL expressions

Search indexes provide the same features as SQL expressions. The following table describes the mappings between features of search indexes and SQL expressions.

SQL expression	Example	Feature of search indexes
without predicate	N/A	Match all query
=	a = 1 b = "hello world"	Term query
>	a > 1	Range query
>=	a >= 2
<	a < 5
<=	a <= 10
is null	a is null	Exists query
is not null	a is not null	Exists query
and	a = 1 and b = "hello world"	Boolean query
or	a > 1 or b = 2
not	not a = 1
!=	a !=1
like	a like "%s%"	Wildcard query
in	a in (1,2,3)	Terms query
text_match	text_match(a, "tablestore cool")	Match query
text_match_phrase	text_match_phrase(a, "tablestore cool")	Match phrase query
array_extract	array_extract(col_long)	Array and Nested data types
nested_query	nested_query(`tags.tagName` = 'tag1' AND `tags.score` = 0.2)	Array and Nested data types Nested query
order by	nested_query col_long	Perform sorting and paging
limit	limit 10	Perform sorting and paging
min()	min(col_long)	Aggregation
max()	max(col_long)
sum()	sum(col_long)
avg()	avg(col_long)
count()	count(col_long)
count(distinct)	count(distinct col_long)
any_value()	any_value(col_long)
group by	group by col_long

References

When you use a search index to accelerate SQL queries, you can use features based on the search index. The features include full-text search, Array type in search indexes, Nested type in search indexes, and virtual columns of search indexes.