Querying Data with Indexes

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.

    Assume that a composite index is created 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 statement is an example:

    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.

    Assume that a composite index is created 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
  Assume that a composite index is created 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))

  

  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.