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. 
SELECT * FROM exampletable WHERE a > 1;  /* Operator pushdown is supported for comparison between data columns and constants. */
SELECT * FROM exampletable WHERE a > b;  /* Operator pushdown is not supported for comparison between data columns. */
Aggregate functions
  • Basic aggregation: MIN, MAX, COUNT, AVG, SUM, and ANY_VALUE
  • Deduplication and aggregation: COUNT(DISTINCT col_name)
  • Grouping: GROUP BY col_name
 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. 
SELECT COUNT(*) FROM exampletable;           /* Operator pushdown is supported for the special usage of COUNT(*). */
SELECT SUM(a) FROM exampletable;             /* Operator pushdown is supported when the function parameter is a data column. */
SELECT a, b FROM exampletable GROUP BY a, b; /* Operator pushdown is supported for grouping by data column. */
SELECT a FROM exampletable GROUP BY a+1;     /* Operator pushdown is not supported for grouping by expression. */
SELECT SUM(a+b) FROM exampletable;           /* Operator pushdown is not supported when the function parameter is an expression. */
LIMIT
  • LIMIT row_count
  • ORDER BY col_name LIMIT row_count
 Operator pushdown is supported only when the parameter in the ORDER BY clause is a data column. 
SELECT * FROM exampletable ORDER BY a LIMIT 1;     /* Operator pushdown is supported for sorting by data column. */
SELECT * FROM exampletable ORDER BY a, b LIMIT 1;  /* Operator pushdown is supported for sorting by data column. */
SELECT * FROM exampletable ORDER BY a+1 LIMIT 1;   /* Operator pushdown is not supported for sorting by expression. */