Querying Data Based on Indexes

You can use SingleColumnValueFilter to query data in a user table that has an index. When the query condition can hit an index, the query speed is much faster than that of the native table query.

How to Use

The index hit rules are as follows:

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 IndexCol1, IndexCol2, and IndexCol3.

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)
Filter_Condition (IndexCol1) AND Filter_Condition (NonIndexCol1)
Filter_Condition (IndexCol1) AND Filter_Condition (IndexCol2) AND Filter_Condition (NonIndexCol1)

The index does not take effect under the following conditions:

Filter_Condition (IndexCol2) AND Filter_Condition (IndexCol3)
Filter_Condition (IndexCol2)
Filter_Condition (IndexCol3)
Filter_Condition (NonIndexCol1)

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), using the index improves the query performance.
```
Filter_Condition (IndexCol1) OR Filter_Condition (IndexCol1) OR Filter_Condition (IndexCol1)
```
- When multiple index columns are filtered, no index will be hit and the query performance is not improved.
```
Filter_Condition (IndexCol1) OR Filter_Condition (IndexCol2)
```
- When index and non-index columns are filtered, no index will be hit and the query performance is not improved.
```
Filter_Condition (IndexCol1) OR Filter_Condition (NonIndexCol1)
```
- In special cases, when each part of the OR condition can hit an index, the query performance is improved.
```
(Filter_Condition (IndexCol1) AND Filter_Condition (NonIndexCol1)) OR (Filter_Condition (IndexCol1) AND Filter_Condition (IndexCol2))
```

Suggestions

Ensure that the index column and covering column can completely cover the query column and query condition column. In this way, data can be obtained by querying only the index table and the query performance is improved.
If the query conditions contain more than index columns, ensure that the non-index columns hit the covering columns. Then the query conditions can be used to query the index table to filter more data.
Reduce the use of OR conditions, especially the combination of OR conditions and range conditions. Even when an index is hit, large-scale query is required and the query speed is slow.
Because index columns are ordered, the query condition should have as many consecutive index columns as possible. The conditions of the index columns that are consecutive from the beginning can only be one or more specified values. If the conditions are a range, the consecutive index columns end at the index column whose condition is a range. The subsequent index columns are used as filters for querying the index table. If more index columns are used, the filtering capability is stronger and the query performance is higher.
The index table does not inherit the timestamp of the data table. Therefore, you are not advised to use the timestamp of the index query result.

Parent topic: Using the Feature