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 speeds up 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. For more information, see the Create a data table section in Manage the Wide Column model in the Tablestore console.

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 the Create a data table section in Manage the Wide Column model in the Tablestore console.
  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 required data table.
  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 STRING, INTEGER, BINARY, FLOAT, and BOOLEAN data types. 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 fig_20221020_delete icon next to a predefined column to remove the predefined column.
    fig_202220_adddefinedcolumn
  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 fig_20221020_delete icon in the Remove Pre-Defined Column column.

Step 2: Create a secondary index

  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 required data table.
  2. On the Indexes tab, click Create Secondary Index.
  3. In the Create Index dialog box, configure the parameters.
    fig_20221020_indexsecondary001
    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.
      Index Type The type of the secondary index. Valid values:
      • Global Secondary Index: Tablestore automatically synchronizes the 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 a predefined column of the data table.

      • Local Secondary Index: Tablestore automatically synchronizes the 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 in sequence for the secondary index based on the index type that you specified and click Add Primary Key Column. Each time you select a primary key column, you must click Add 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. Each time you select a predefined column, you must click Add Pre-defined 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.

Perform a range query

  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 required data table.
  2. On the Indexes tab, find the index that you want to use to query data and click Query in the Actions column.
  3. In the Search dialog box, specify query conditions.
    fig_20221020_rangequery
    1. Set Modes to Range Search.
    2. 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 (,).
    3. Configure the Start Primary Key Column and End Primary Key Column parameters.
      Note
      • If you set Modes to Range Search, the range that is specified by the start value and the end value in the right primary key column takes effect only when the start value and the end value are the same within each leftmost primary key column. If the start value and the end value in a leftmost primary key column are different, the range that is specified by the start value and the end value 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 Max Versions.
    5. Set Sequence to Forward Search or Backward Search.
  4. Click OK.
    Data that meets the query conditions is displayed in the data list.

Perform a single-row query

  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 required data table.
  2. On the Indexes tab, find the index that you want to use to query data and click Query in the Actions column.
  3. In the Search dialog box, specify query conditions.
    fig_20221020_querydatasingle
    1. Set Modes to Get Row.
    2. 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 (,).
    3. 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.
    4. Retain the default value of Max Versions.
  4. 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.