Querying Data with Indexes

Updated on 2024-11-29 GMT+08:00

View PDF

Index-based Query

You can use SingleColumnValueFilter to query data in a table with indexes. When the query condition hits an index, the query speed is much faster than that of an ordinary table query.

Typical index query conditions are as follows:

Query by multiple AND conditions
- When the columns used for a query contain at least the first indexed column, the query performance is optimized.
  For example, create a composite index for C1, C2, and C3.
  
  The index takes effect in the following queries:
  
  Filter_Condition (IndexCol1) AND Filter_Condition (IndexCol2) AND Filter_Condition (IndexCol3)
  
  Filter_Condition (IndexCol1) AND Filter_Condition (IndexCol2)
  
  Filter_Condition (IndexCol1) AND Filter_Condition (IndexCol3)
  
  Filter_Condition (IndexCol1)
  
  The index does not take effect in the following queries:
  
  Filter_Condition (IndexCol2) AND Filter_Condition (IndexCol3)
  
  Filter_Condition (IndexCol2)
  
  Filter_Condition (IndexCol3)
- When you use "Index Column AND Non-Index Column" as a query condition, the index can improve query performance. If a non-index column hits a covering column, the query performance is optimal. If a non-index column needs to be frequently queried, you are advised to define it as a covering column. The following are examples:
  Filter_Condition (IndexCol1) AND Filter_Condition (NonIndexCol1)
  
  Filter_Condition (IndexCol1) AND Filter_Condition (IndexCol2) AND Filter_Condition (NonIndexCol1)
- When multiple columns are used for query, you can specify a value range for only the last column in the composite index and set other columns to specified values
  For example, create a composite index for C1, C2, and C3. In a range query, only the value range of C3 can be set. The search criteria are "C1 = XXX, C2 = XXX, and C3 = Value range."
Query by multiple OR conditions
For example, create a composite index for C1, C2, and C3.
- If only the first field in the index column is searched (range filtering is supported), indexing improves the query performance.
  Filter_Condition (IndexCol1) OR Filter_Condition (IndexCol1) OR Filter_Condition (IndexCol1)
- When non-index and non-index columns are searched, indexes cannot be hit, and query performance is not improved.
  Filter_Condition (IndexCol1) OR Filter_Condition (NonIndexCol1)
- During a combined query, if the outermost layer contains the OR condition, the index cannot be hit, and the query performance is not improved.
  Filter_Condition (IndexCol1) OR Filter_Condition (NonIndexCol1)
  
  (Filter_Condition(IndexCol1)AND Filter_Condition (IndexCol2) )OR(Filter_Condition(NonIndexCol1))
NOTE:

Reduce the use of OR conditions, especially an OR condition used together with a range condition. Otherwise, large-scale data is queried in slow speed when indexes are hit.