Help Center/ MapReduce Service/ Component Operation Guide (LTS)/ Using HBase/ Using a Global Secondary Indexes/ Querying Data Based on Indexes
Updated on 2024-05-29 GMT+08:00
View PDF

Querying Data Based on Indexes

Index-based query

In a user table with an index, you can use SingleColumnValueFilter to query data. When the query condition can match the index, the query speed is much faster than that of the original table query.

The index matching rules are as follows:

  • Query by Multiple AND Conditions
    • When the columns used for query contain at least the first column of the index, using the index improves the query performance.

      For example, create a combination index for C1, C2, and C3.

      The index takes effect in the following situations:

      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 situations:

      Filter_Condition (IndexCol2) AND Filter_Condition (IndexCol3)

      Filter_Condition (IndexCol2)

      Filter_Condition (IndexCol3)

    • When you use Index Column AND Non-Index Column for filtering in the query, the index can improve query performance. If a non-index column hits an overwrite column, the query performance is optimal. If a non-index column needs to be frequently queried, you are advised to define it as an overwrite column. The following 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 combination index and set other columns to specified values

      For example, create a combination index for C1, C2, and C3. In a range query, only the value range of C3 can be set. The filter criteria are "C1 = XXX, C2 = XXX, and C3 = Value range."

  • Query by Multiple OR Conditions
    For example, create a combination index for C1, C2, and C3.
    • When only the first field in the index column is filtered (range filtering is supported), the index can be used to improve the query performance.

      Filter_Condition (IndexCol1) OR Filter_Condition (IndexCol1) OR Filter_Condition (IndexCol1)

    • When non-index and non-index columns are filtered, indexes cannot be hit, and the query performance is not improved.

      Filter_Condition (IndexCol1) OR Filter_Condition (NonIndexCol1)

    • During combined query, if the outermost layer contains the OR condition, the index cannot be matched, 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 OR conditions and range conditions. When indexes are matched, large-scale query is performed and the query speed is slow.