Use a secondary index in the console to accelerate data queries - Tablestore

The secondary index feature allows you to query data based on the primary key of a data table and the index columns of the secondary index that is created for the table. This accelerates data queries. When you create a secondary index, you can set index columns or attribute columns of the secondary index to the predefined columns that you specified for the table for which you want to create the secondary index. After you create a secondary index, you can use the secondary index to query data.

Prerequisites

A data table for which the Max Versions parameter is set to 1 is created. One of the following conditions is met by the TTL parameter of the data table:

The TTL parameter of the data table is set to -1, which means that data in the data table never expires.
If the Time to Live (TTL) of the data table is not -1, the table does not allow updates. This means the Allow Updates option is set to No.

Note

The TTL of the secondary index is the same as the TTL of the data table.

Step 1: (Optional) Add predefined columns

If you create a secondary index for a table and the table does not contain predefined columns or the existing predefined columns do not meet your business requirements, you can add or remove the predefined columns in the table.

Note

You can also add predefined columns when you create a data table. For more information, see Step 3: Create a data table.

Go to the Manage Table page.
1. Log on to the Tablestore console.
2. On the Overview page, click the instance name.
3. On the Instance Details tab, in the Tables section, click the data table name.
On the Basic Information tab, in the Pre-defined Column section of the Advanced Features module, click Add Pre-Defined Column.
In the Add Pre-Defined Column dialog box, click Add Pre-Defined Column, and then enter a name and select a data type for the predefined column.
- Naming conventions for predefined columns: The name must be 1 to 255 bytes in length and can contain letters, digits, and underscores (_). The name must start with a letter or an underscore (_).
- You can select STRING, INTEGER, BINARY, FLOAT, or BOOLEAN as the data type. If the predefined column is used as an index column, select STRING, INTEGER, or BINARY.
Note
You can click Add Pre-Defined Column to add multiple columns. To remove an unwanted column, click the icon next to it.
Click OK.

The predefined columns that you added are displayed in the list of predefined columns.

If you want to remove a predefined column from the list of predefined columns, click the icon in the Remove Pre-Defined Column column.

Step 2: Create a secondary index

You can create a secondary index and use the secondary index to accelerate data queries. Secondary indexes consist of global secondary indexes and local secondary indexes. You can create a secondary based on your business requirements.

Note

You can create a secondary index when you create a data table. For more information, see Step 3: Create a data table.

Go to the Manage Table page.
1. Log on to the Tablestore console.
2. On the Overview page, click the instance name.
3. On the Instance Details tab, in the Tables section, click the data table name.
On the Indexes tab, click Create a secondary index.

In the Create Index dialog box, configure the secondary index.

fig_20221020_indexsecondary001

The following table describes the parameters.

Parameter	Description
Index Type	The type of the index. This value is always Secondary Index.
Instance Name	The name of the instance. You cannot change the value of this parameter.
Table Name	The name of the data table for which you want to create the secondary index. You cannot change the value of this parameter.
Index Name	The name of the secondary index that you want to create. Important The name cannot be the same as the name of existing data tables and time series tables.
Index Type	The type of the secondary index. Valid values: Global Secondary Index: Tablestore automatically synchronizes data from the indexed columns and primary key columns of the data table to the columns of the index table that you want to create in asynchronous mode. The first primary key column of the index table can be a primary key column or predefined column of the data table. Local Secondary Index: Tablestore automatically synchronizes data from the indexed columns and primary key columns of the data table to the columns of the index table that you want to create in synchronous mode. After data is written to the data table, you can immediately query the data in the index table. The first primary key column of the index table must be the first primary key column of the data table.
Existing Data	Specifies whether to include existing data of the data table in the secondary index. Valid values: Include Existing Data Exclude Existing Data

Select the primary key columns in the desired order and click Add Primary Key Column for each column.

Only predefined columns of the STRING, INTEGER, and BINARY data types can be used as primary key columns.
Select the predefined columns to use as attribute columns, and then click Add Pre-defined Column.

Click OK.

The secondary index that you created is displayed in the list of indexes.

Step 3: Query data

You can use single-row queries or range queries for index tables to query the required data.

Perform a range query

Go to the Manage Table page.
1. Log on to the Tablestore console.
2. On the Overview page, click the instance name.
3. On the Instance Details tab, in the Tables section, click the data table name.
On the Indexes tab, find the target index table and click Query in the Actions column.
In the Search dialog box, set the query conditions.
1. Set Mode to Range Search.
2. By default, all columns are returned. To return specific attribute columns, turn off Get All Columns and enter the column names, separated by commas (,).
3. Configure the Start Primary Key Column and End Primary Key Column parameters.
  Important
  
  If you set the Modes parameter to Range Search, the range that is specified by the start and end values in the right primary key column takes effect only if the start and end values are the same within each leftmost primary key column. If the start and end values in a leftmost primary key column are different, the range that is specified by the start and end values in the right primary key column does not take effect.
  
  The range that is supported for range queries is a left-open, right-closed interval.
4. Retain the default value of the Max Versions parameter.
5. Set the sort direction for the query results to Forward Search or Backward Search.
Click OK.

Data that meets the query conditions is displayed in the data list.

Perform a single-row query

Go to the Manage Table page.
1. Log on to the Tablestore console.
2. On the Overview page, click the instance name.
3. On the Instance Details tab, in the Tables section, click the data table name.
On the Indexes tab, find the target index table and click Query in the Actions column.
In the Search dialog box, set the query conditions.
1. Set Mode to Get Row.
2. By default, all columns are returned. To return specific attribute columns, turn off Get All Columns and enter the column names, separated by commas (,).
3. Enter the Primary Key Value for the target row.
  
  The integrity and accuracy of the primary key value affect the query results.
4. Retain the default value of the Max Versions parameter.
Click OK.

If the row that you want to query is included in the index table, Tablestore returns the data of the row. If the row that you want to query is not included in the index table, no data is returned.

FAQ

References

You can use secondary indexes by using Tablestore SDKs or the Tablestore CLI. For more information, see Use global secondary index by using Tablestore SDK and Secondary index.
If you want to query data in a more efficient and flexible manner, you can use the search index feature. The feature provides multiple query methods, including Boolean query, full-text search, prefix query, and fuzzy query. For more information, see Overview.