A search index provides features such as conditional filtering, aggregation, and sorting. After you create a search index, the system can make full use of the computing power of the search index and push some SQL computing tasks down to the search index for execution. This avoids full table scans and improves computing efficiency.
Prerequisites
A search index is created. For more information about how to create a search index, see Create search indexes.
Scenarios
If a search index contains the data columns involved in SQL statements, the SQL engine reads data by using the search index and pushes down the operators that are supported by the search index. For example, a table named exampletable has a, b, c, and d columns. A search index of the table contains the b, c, and d columns that are indexed. The SQL engine reads data by using the search index when only the b, c, and d columns are involved in SQL statements.
SELECT a, b, c, d FROM exampletable; /* The search index does not contain the a column. The SQL engine reads data by scanning the entire table and does not push down operators. */
SELECT b, c, d FROM exampletable; /* The search index contains the b, c, and d columns. The SQL engine reads data by using the search index and pushes down operators. */
Operators that can be pushed down
Type | Operator | Pushdown limit |
---|---|---|
Logical operators | AND and OR | The NOT operator cannot be pushed down. |
Relational operators | =, !=, <, <=, >, >=, and BETWEEN ... AND ... | Operator pushdown is supported only for comparison between data columns and constants.
Operator pushdown is not supported for comparison between data columns.
|
Aggregate functions |
|
An aggregate function can aggregate all data or data in a GROUP BY group. Operator
pushdown is supported only when the aggregate function supports pushdown and the function
parameter is a data column.
|
LIMIT |
|
Operator pushdown is supported only when the parameter in the ORDER BY clause is a
data column.
|