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 TTL parameter of the data table is not set to -1, the Allow Updates parameter is set to No.
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.
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.
Log on to the Tablestore console.
On the Overview page, click the name of an instance.
In the Tables section of the Instance Details tab, click the name of the data table that you want to manage.
In the Pre-defined Column section of the Advanced Features module on the Basic Information tab, click Add Pre-defined Column.
In the Add Pre-Defined Column dialog box, click + Add next to Pre-defined Column. Specify a name for the predefined column and select a data type from the drop-down list.
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 (_).
Predefined columns support the following data types: STRING, INTEGER, BINARY, FLOAT, and BOOLEAN. If you want to use the predefined column as an index column of a secondary index, select STRING, INTEGER, or BINARY from the drop-down list for the predefined column.
NoteAfter you click Add Pre-Defined Column, you can add multiple predefined columns in a batch. You can click the icon next to a predefined column to remove the predefined column.
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.
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.
Log on to the Tablestore console.
On the Overview page, click the name of an instance.
In the Tables section of the Instance Details tab, click the name of the data table that you want to manage.
On the Indexes tab, click Create Secondary Index.
In the Create Index dialog box, configure the parameters.
The following table describes the parameters.
Parameter
Description
Index Type
The type of the index. The value of this parameter is set to Secondary Index and cannot be changed.
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.
ImportantThe 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 for the secondary index in sequence based on the index type that you specified and then click Add Primary Key Column. You must click Add Primary Key Column each time you select a primary key column.
You can set primary key columns to predefined columns only of the STRING, INTEGER, or BINARY type.
Select the predefined columns that you want to use as attribute columns for the secondary index and click Add Pre-defined Column. You must click Add Pre-defined Column each time you select a predefined 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.
Log on to the Tablestore console.
On the Overview page, click the name of an instance.
In the Tables section of the Instance Details tab, click the name of the data table that you want to manage.
On the Indexes tab, find the index that you want to use to query data and click Query in the Actions column.
In the Search dialog box, specify query conditions.
Set the Modes parameter to Range Search.
By default, the system returns all attribute columns. To return specific attribute columns, turn off All Columns and specify the attribute columns that you want to return. Separate multiple attribute columns with commas (,).
Configure the Start Primary Key Column and End Primary Key Column parameters.
ImportantIf 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.
Retain the default value of the Max Versions parameter.
Set the Sequence parameter 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.
Log on to the Tablestore console.
On the Overview page, click the name of an instance.
In the Tables section of the Instance Details tab, click the name of the data table that you want to manage.
On the Indexes tab, find the index that you want to use to query data and click Query in the Actions column.
In the Search dialog box, specify query conditions.
Set the Modes parameter to Get Row.
By default, the system returns all attribute columns. To return specific attribute columns, turn off All Columns and specify the attribute columns that you want to return. Separate multiple attribute columns with commas (,).
Configure the Primary Key Value parameter of the row that you want to query.
The integrity and accuracy of the primary key value affect the query results.
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.