Index selection and usage best practices

Secondary index

A Secondary index is equivalent to an index table that reorders the specified attribute columns as the primary key. Like a data table, it stores data ordered by primary key, and its query capabilities and data scale are the same as a data table—the only difference is the primary key. Queries locate data directly through the primary key of the index table, with efficiency comparable to a primary key query on a data table.

Secondary indexes are divided into global secondary indexes and local secondary indexes, which differ in synchronization method, primary key requirements, and consistency (strong consistency means the latest data is readable immediately after a write, and eventual consistency means there is a brief delay before the data is readable):

Secondary indexes suit scenarios with fixed query dimensions, where each query dimension requires a separate index. Take a data table that stores file hashes as an example, where the primary key column is the file path (FilePath) and the attribute columns are MD5 and SHA1:

You can query a file's hash by primary key, but you cannot look up a file name by MD5 or SHA1. To do this, create a global secondary index with MD5 as the primary key for this table (SHA1 works the same way):

Search index

A Search index is a combination of multiple data structures built on inverted indexes, BKD-Trees, and other structures. It supports multi-condition combined queries, full-text search, fuzzy queries, geolocation queries, and statistical aggregation. A single search index can cover any combination of multiple query conditions, without creating a separate index for each combination. For example, an order table with 16 fields may be queried by any combination of fields, such as salesperson, product type, and price range. A single search index that covers these fields handles all such combinations.

Scenarios that do not require an index

An index is not required in the following scenarios:

• Primary key queries and primary key range queries already meet the business needs.

• You filter data within a certain range, and the amount of data in the range is small or the query frequency is low. In this case, use a filter. For more information, see Filters.

• A complex query runs infrequently and is not latency-sensitive. In this case, use a SQL query. For more information, see SQL query.

Secondary index versus search index

Secondary indexes and search indexes differ in query flexibility, performance, and feature support. Use the following table to choose the appropriate index type:

Recommendation: Choose a secondary index when the query dimensions are fixed and you need low latency or strong consistency. Choose a search index when the query dimensions are varied, or when you need full-text search, fuzzy queries, or geolocation queries.

Index combination strategies

When the data scale is very large, flexible queries come at a higher cost. For a trillion-row dataset, the options trade off cost against query flexibility:

• Data table only (no index): Lowest cost, but limited query flexibility.

• Search index: Flexible queries, but higher cost.

• Secondary index: Lower cost, but only suits fixed-dimension queries.

Ultra-large datasets usually have a time attribute (such as device monitoring data or user behavior data). You can use the following combination strategies to balance query flexibility and cost:

• Metadata table with a search index, full dataset table with a secondary index or no index. A metadata table is the primary table that generates data (such as a device information table or a user information table). It has a relatively small amount of data but many query dimensions, which suits a search index. A full dataset table has a large amount of data but fixed query dimensions, so a secondary index or primary key query is sufficient.

• Hot data with a search index, cold data with a secondary index or no index. Many scenarios require multi-dimensional queries only for hot data, while fixed-dimension queries are sufficient for cold data. Separating hot and cold data provides a better cost-performance ratio. Search indexes support time to live (TTL), so you can use search index TTL to distinguish hot data from cold data. For more information, see Search index lifecycle.

Field type planning

Different field types use different underlying data structures, so choosing the right type significantly improves query performance. Plan field types based on your query needs before writing data to the data table:

If you have already used an unsuitable field type and modifying existing data is inconvenient, use the Dynamically modify schema feature for a smooth upgrade, or use virtual columns to adjust the field type.

Index presorting

If most queries return results in the same sort order, you can configure presorting to save sorting time. The larger the number of matched rows, the more noticeable the performance gain from presorting. By default, a search index presorts by all primary keys of the data table. Currently, custom presorting can only be configured through the SDK.

Index routing optimization

By default, a search index hash-partitions data based on all primary keys of the data table, so a query must access all partitions of the index engine. If a query always includes a specific field (such as the user ID in an e-commerce app), set that field as the routing key. Data with the same routing key value then falls on one or more specific partitions. Routing keys provide the following benefits:

• Significantly reduce long-tail queries: Without a routing key, every query accesses all partitions, so the overall latency depends on the slowest partition. A spike or network stall on a single partition slows down the entire request.

• Support higher queries per second (QPS): A routed request accesses only some partitions, with no read amplification, and consumes fewer cluster resources.

• No scale limit: When the routing key is well designed, a search index has no scale limit.

Note the following when you use a routing key:

• The routing key values should be as diverse as possible, and the amount of data per routing key should stay within a reasonable limit (for example, no more than 100 million rows). If a single routing key has too much data, concatenate multiple unchanging fields into the routing key.

• After you set a routing key, if you do not specify the routing key in a query, all partitions are accessed, but the completeness of the query results is not affected.

• Routing optimization is not needed when the query QPS is low or the data size is less than 200 million rows.

Logical-to-physical field mapping

When you have a large number of users, each with different column names, the personalized fields of all users combined can exceed the maximum number of fields in a search index. In this case, use a fixed set of physical fields that all users share.

How reuse works: Different users' data is stored in different rows, so the same physical field can carry different logical fields in different users' rows. For example, the physical field keyword_1 stores field a in user 1's row and field b in user 2's row. A meta mapping table then records the field mapping for each user.

Assuming each user uses at most 10 Keyword-type index fields, the design steps are as follows:

1. Design the index: Create fixed physical fields based on the number of fields a single user needs. For example, if each user has at most 10 fields, create keyword_1 through keyword_10 (add long_1 through long_10 if you need the Long type), along with other non-personalized fields required by the business. No matter how many users you have, the search index needs only this single set of physical fields.

2. Prepare the meta mapping table: Record the mapping from each user's logical field names to physical field names. For example, user 1's fields a, b, and c map to keyword_1, keyword_2, and keyword_3 respectively; user 2's fields b, c, and d map to keyword_1, keyword_2, and keyword_3 respectively. The two users reuse the same physical fields without conflict, because their data is in different rows. If the mapping table is small, cache it in memory.

3. Read and write data by mapping: When writing, use the mapping table to write each user's logical field values to the corresponding physical fields. When querying, use the mapping table to convert the query conditions. For example, user 2 querying b=4 and d=5 is converted to keyword_1=4 and keyword_3=5.

Choose the appropriate query method

Choose the query method that matches your field type and query needs for optimal performance:

Simplify query conditions

Too many conditions, deep nesting, or a large TermsQuery can increase query latency. Simplify the query conditions and keep only the necessary filters. The server automatically rewrites and optimizes queries, so you generally do not need to take extra action. If query latency is high, contact Tablestore technical support to optimize the query.

Deep pagination and token encoding

For deep pagination, we recommend the token-based pagination method. To persist a token (byte[] type), encode it to a string with Base64. Converting the token directly with new String(token) loses the token content.