Help Center/ MapReduce Service/ Component Operation Guide (LTS) (Ankara Region)/ Using HBase/ Using Global Secondary Indexes/ Querying Data with Indexes
Updated on 2024-11-29 GMT+08:00
View PDF
Share

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.

      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))

    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.