Tablestore:Use secondary indexes in the Tablestore console

Step 1: (Optional) Add predefined columns

1. Go to the Manage Table page.

   1. Log on to the Tablestore console.

   2. On the Overview page, click the name of an instance.

   3. In the Tables section of the Instance Details tab, click the name of the data table that you want to manage.

2. In the Pre-defined Column section of the Advanced Features module on the Basic Information tab, click Add Pre-defined Column.

3. 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.

   Note

   After 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.

   

4. 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.

1. Go to the Manage Table page.

   1. Log on to the Tablestore console.

   2. On the Overview page, click the name of an instance.

   3. In the Tables section of the Instance Details tab, click the name of the data table that you want to manage.

2. On the Indexes tab, click Create Secondary Index.

3. In the Create Index dialog box, configure the parameters.

   

   1. 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. 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

   2. 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.

   3. 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.

4. 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.

FAQ

• How do I select between a secondary index and a search index?

• How do I use predefined columns?

• What do I do if the "Don't support allow update operation on table with index and ttl" error message appears when I create a secondary index?

• What do I do if the "OTSErrorMsg: Disallow read index table in building base state" error message appears when I read data from a search index?

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.