Use a secondary index to accelerate data queries by using Tablestore SDKs - Tablestore

A secondary index lets you query rows using columns other than the primary key of a data table. When a query on the primary key alone cannot efficiently retrieve the data you need, create a secondary index on the relevant predefined columns to accelerate lookups. After the index is created, query it directly instead of scanning the full data table.

Prerequisites

Before you begin, ensure that:

The data table has Max Versions set to 1.
The TTL of the data table is set to -1 (data never expires), or the Allow Updates parameter is set to No.

Note

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

Step 1: (Optional) Manage predefined columns

A secondary index can only use columns declared as predefined columns when the data table was created. If the data table has no predefined columns, or the existing ones do not match your indexing needs, add or remove predefined columns before creating the index.

The examples below use Tablestore SDK for Java. Tablestore SDK for Go is also supported.

Add a predefined column

Method

public AddDefinedColumnResponse addDefinedColumn(AddDefinedColumnRequest addDefinedColumnRequest) throws TableStoreException, ClientException

Parameters

Parameter

Type

Description

tableName (required)

String

The name of the data table.

definedColumns (required)

List

The predefined column information. Each predefined column contains the following parameters:

name (String, required): The name of the predefined column.
type (DefinedColumnType, required): The data type of the predefined column. Valid values: STRING, INTEGER, BINARY, DOUBLE, and BOOLEAN.

Sample code

The following sample adds a String-type predefined column named name to test_table.

public static void addDefinedColumnExample(SyncClient client) {
    AddDefinedColumnRequest addDefinedColumnRequest = new AddDefinedColumnRequest();
    addDefinedColumnRequest.setTableName("test_table");
    addDefinedColumnRequest.addDefinedColumn("name", DefinedColumnType.STRING);
    client.addDefinedColumn(addDefinedColumnRequest);
}

Delete a predefined column

Method

public DeleteDefinedColumnResponse deleteDefinedColumn(DeleteDefinedColumnRequest deleteDefinedColumnRequest) throws TableStoreException, ClientException

Parameters

Parameter	Type	Description
tableName (required)	String	The name of the data table.
definedColumns (required)	List<String>	The names of the predefined columns to delete.

Sample code

The following sample deletes the predefined column named name from test_table.

public static void deleteDefinedColumnExample(SyncClient client) {
    DeleteDefinedColumnRequest deleteDefinedColumnRequest = new DeleteDefinedColumnRequest();
    deleteDefinedColumnRequest.setTableName("test_table");
    deleteDefinedColumnRequest.addDefinedColumn("name");
    client.deleteDefinedColumn(deleteDefinedColumnRequest);
}

Step 2: Create a secondary index

Call the CreateIndex operation to create an index table for an existing data table to accelerate data queries. Secondary indexes are classified into global and local secondary indexes. You can create a global or local secondary index based on your business requirements.

Note

You can also create one or more index tables at the same time as the data table by calling the CreateTable operation. For more information, see Create a data table.

The examples below use Tablestore SDK for Java. The following SDKs are also supported: Go, Python, Node.js, .NET, and PHP.

Create a global secondary index

The following sample creates a global secondary index. The index uses DEFINED_COL_NAME_1 as its first primary key column and PRIMARY_KEY_NAME_2 as its second. DEFINED_COL_NAME_2 is included as an attribute column so it can be read directly from the index without looking up the data table.

Set IncludeBaseData to true to include existing data table rows in the index. The time required to backfill existing data varies based on data volume.

private static void createIndex(SyncClient client) {
    IndexMeta indexMeta = new IndexMeta("<INDEX_NAME>");
    // Set DEFINED_COL_NAME_1 as the first primary key column of the index.
    indexMeta.addPrimaryKeyColumn(DEFINED_COL_NAME_1);
    // Set PRIMARY_KEY_NAME_2 as the second primary key column of the index.
    indexMeta.addPrimaryKeyColumn(PRIMARY_KEY_NAME_2);
    // Include DEFINED_COL_NAME_2 as an attribute column for direct reads from the index.
    indexMeta.addDefinedColumn(DEFINED_COL_NAME_2);
    // Create the index without backfilling existing data.
    // Set the third parameter to true to include existing data.
    CreateIndexRequest request = new CreateIndexRequest("<TABLE_NAME>", indexMeta, false);
    client.createIndex(request);
}

Create a local secondary index

The following sample creates a local secondary index. The first primary key column of the index (PRIMARY_KEY_NAME_1) must match the first primary key column of the data table. The index type is set to IT_LOCAL_INDEX and the update mode to IUM_SYNC_INDEX (synchronous update).

Set IncludeBaseData to true to include existing data table rows in the index. The time required to backfill existing data varies based on data volume.

private static void createIndex(SyncClient client) {
    IndexMeta indexMeta = new IndexMeta("<INDEX_NAME>");
    // The first primary key column of a local secondary index must match
    // the first primary key column of the data table.
    indexMeta.addPrimaryKeyColumn(PRIMARY_KEY_NAME_1);
    indexMeta.addPrimaryKeyColumn(DEFINED_COL_NAME_1);
    indexMeta.addDefinedColumn(DEFINED_COL_NAME_2);
    indexMeta.setIndexType(IT_LOCAL_INDEX);
    indexMeta.setIndexUpdateMode(IUM_SYNC_INDEX);
    // Create the index without backfilling existing data.
    // Set the third parameter to true to include existing data.
    CreateIndexRequest request = new CreateIndexRequest("<TABLE_NAME>", indexMeta, false);
    client.createIndex(request);
}

Step 3: Read data from the index table

The read path depends on which attribute columns you need:

Columns are in the index table — read directly from the index table.
Columns are not in the index table — scan the index table to get the primary key of each matching row, then fetch those columns from the data table. This two-step approach incurs an extra read per row, so include frequently queried attribute columns in the index when you create it.

The examples below use Tablestore SDK for Java. The following SDKs are also supported: Go, Python, Node.js, .NET, and PHP.

Read a single row

Build the primary key of the index table and call getRow. The following sample reads a single row, then reads again with a specific column filter.

private static void getRowFromIndex(SyncClient client) {
    // Build the primary key of the index table.
    // For a local secondary index, the first primary key column must match
    // the first primary key column of the data table.
    PrimaryKeyBuilder primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    primaryKeyBuilder.addPrimaryKeyColumn(DEFINED_COL_NAME_1, PrimaryKeyValue.fromString("def1"));
    primaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_1, PrimaryKeyValue.fromLong(100));
    primaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_2, PrimaryKeyValue.fromString("pri1"));
    PrimaryKey primaryKey = primaryKeyBuilder.build();

    SingleRowQueryCriteria criteria = new SingleRowQueryCriteria("<INDEX_NAME>", primaryKey);
    criteria.setMaxVersions(1);
    GetRowResponse getRowResponse = client.getRow(new GetRowRequest(criteria));
    Row row = getRowResponse.getRow();
    // Returns null if the row does not exist.
    System.out.println("Read result: " + row);

    // Read a specific column.
    criteria.addColumnsToGet("Col0");
    getRowResponse = client.getRow(new GetRowRequest(criteria));
    row = getRowResponse.getRow();
    System.out.println("Read result: " + row);
}

Read a range of rows

Set a start and end primary key on the index table and call getRange in a loop until nextStartPrimaryKey is null.

Use a global secondary index

When all required columns are in the index table, read directly from the index.

private static void scanFromIndex(SyncClient client) {
    RangeRowQueryCriteria rangeRowQueryCriteria = new RangeRowQueryCriteria("<INDEX_NAME>");

    PrimaryKeyBuilder startPrimaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    startPrimaryKeyBuilder.addPrimaryKeyColumn(DEFINED_COL_NAME_1, PrimaryKeyValue.INF_MIN);
    startPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_1, PrimaryKeyValue.INF_MIN);
    startPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_2, PrimaryKeyValue.INF_MIN);
    rangeRowQueryCriteria.setInclusiveStartPrimaryKey(startPrimaryKeyBuilder.build());

    PrimaryKeyBuilder endPrimaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    endPrimaryKeyBuilder.addPrimaryKeyColumn(DEFINED_COL_NAME_1, PrimaryKeyValue.INF_MAX);
    endPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_1, PrimaryKeyValue.INF_MAX);
    endPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_2, PrimaryKeyValue.INF_MAX);
    rangeRowQueryCriteria.setExclusiveEndPrimaryKey(endPrimaryKeyBuilder.build());

    rangeRowQueryCriteria.setMaxVersions(1);

    System.out.println("Scan result of the index table:");
    while (true) {
        GetRangeResponse getRangeResponse = client.getRange(new GetRangeRequest(rangeRowQueryCriteria));
        for (Row row : getRangeResponse.getRows()) {
            System.out.println(row);
        }
        if (getRangeResponse.getNextStartPrimaryKey() != null) {
            rangeRowQueryCriteria.setInclusiveStartPrimaryKey(getRangeResponse.getNextStartPrimaryKey());
        } else {
            break;
        }
    }
}

When the required columns are not in the index table, scan the index to get the primary key of each matching row, then fetch those columns from the data table.

private static void scanFromIndex(SyncClient client) {
    RangeRowQueryCriteria rangeRowQueryCriteria = new RangeRowQueryCriteria("<INDEX_NAME>");

    PrimaryKeyBuilder startPrimaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    startPrimaryKeyBuilder.addPrimaryKeyColumn(DEFINED_COL_NAME_1, PrimaryKeyValue.INF_MIN);
    startPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_1, PrimaryKeyValue.INF_MIN);
    startPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_2, PrimaryKeyValue.INF_MIN);
    rangeRowQueryCriteria.setInclusiveStartPrimaryKey(startPrimaryKeyBuilder.build());

    PrimaryKeyBuilder endPrimaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    endPrimaryKeyBuilder.addPrimaryKeyColumn(DEFINED_COL_NAME_1, PrimaryKeyValue.INF_MAX);
    endPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_1, PrimaryKeyValue.INF_MAX);
    endPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_2, PrimaryKeyValue.INF_MAX);
    rangeRowQueryCriteria.setExclusiveEndPrimaryKey(endPrimaryKeyBuilder.build());

    rangeRowQueryCriteria.setMaxVersions(1);

    while (true) {
        GetRangeResponse getRangeResponse = client.getRange(new GetRangeRequest(rangeRowQueryCriteria));
        for (Row row : getRangeResponse.getRows()) {
            // Extract the data table primary key from the index row.
            PrimaryKey curIndexPrimaryKey = row.getPrimaryKey();
            PrimaryKeyColumn pk1 = curIndexPrimaryKey.getPrimaryKeyColumn(PRIMARY_KEY_NAME_1);
            PrimaryKeyColumn pk2 = curIndexPrimaryKey.getPrimaryKeyColumn(PRIMARY_KEY_NAME_2);
            PrimaryKeyBuilder mainTablePKBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
            mainTablePKBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_1, pk1.getValue());
            mainTablePKBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_2, pk2.getValue());
            PrimaryKey mainTablePK = mainTablePKBuilder.build();

            // Fetch the required columns from the data table.
            SingleRowQueryCriteria criteria = new SingleRowQueryCriteria("<TABLE_NAME>", mainTablePK);
            criteria.addColumnsToGet(DEFINED_COL_NAME_3);
            criteria.setMaxVersions(1);
            GetRowResponse getRowResponse = client.getRow(new GetRowRequest(criteria));
            Row mainTableRow = getRowResponse.getRow();
            System.out.println(row);
        }
        if (getRangeResponse.getNextStartPrimaryKey() != null) {
            rangeRowQueryCriteria.setInclusiveStartPrimaryKey(getRangeResponse.getNextStartPrimaryKey());
        } else {
            break;
        }
    }
}

Use a local secondary index

When all required columns are in the index table, read directly from the index.

private static void scanFromIndex(SyncClient client) {
    RangeRowQueryCriteria rangeRowQueryCriteria = new RangeRowQueryCriteria("INDEX_NAME");

    PrimaryKeyBuilder startPrimaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    startPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_1, PrimaryKeyValue.INF_MIN);
    startPrimaryKeyBuilder.addPrimaryKeyColumn(DEFINED_COL_NAME_1, PrimaryKeyValue.INF_MIN);
    startPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_2, PrimaryKeyValue.INF_MIN);
    rangeRowQueryCriteria.setInclusiveStartPrimaryKey(startPrimaryKeyBuilder.build());

    PrimaryKeyBuilder endPrimaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    endPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_1, PrimaryKeyValue.INF_MAX);
    endPrimaryKeyBuilder.addPrimaryKeyColumn(DEFINED_COL_NAME_1, PrimaryKeyValue.INF_MAX);
    endPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_2, PrimaryKeyValue.INF_MAX);
    rangeRowQueryCriteria.setExclusiveEndPrimaryKey(endPrimaryKeyBuilder.build());

    rangeRowQueryCriteria.setMaxVersions(1);

    System.out.println("Scan result of the index table:");
    while (true) {
        GetRangeResponse getRangeResponse = client.getRange(new GetRangeRequest(rangeRowQueryCriteria));
        for (Row row : getRangeResponse.getRows()) {
            System.out.println(row);
        }
        if (getRangeResponse.getNextStartPrimaryKey() != null) {
            rangeRowQueryCriteria.setInclusiveStartPrimaryKey(getRangeResponse.getNextStartPrimaryKey());
        } else {
            break;
        }
    }
}

When the required columns are not in the index table, scan the index to get the primary key of each matching row, then fetch those columns from the data table.

private static void scanFromIndex(SyncClient client) {
    RangeRowQueryCriteria rangeRowQueryCriteria = new RangeRowQueryCriteria("<INDEX_NAME>");

    PrimaryKeyBuilder startPrimaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    startPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_1, PrimaryKeyValue.INF_MIN);
    startPrimaryKeyBuilder.addPrimaryKeyColumn(DEFINED_COL_NAME_1, PrimaryKeyValue.INF_MIN);
    startPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_2, PrimaryKeyValue.INF_MIN);
    rangeRowQueryCriteria.setInclusiveStartPrimaryKey(startPrimaryKeyBuilder.build());

    PrimaryKeyBuilder endPrimaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    endPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_1, PrimaryKeyValue.INF_MAX);
    endPrimaryKeyBuilder.addPrimaryKeyColumn(DEFINED_COL_NAME_1, PrimaryKeyValue.INF_MAX);
    endPrimaryKeyBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_2, PrimaryKeyValue.INF_MAX);
    rangeRowQueryCriteria.setExclusiveEndPrimaryKey(endPrimaryKeyBuilder.build());

    rangeRowQueryCriteria.setMaxVersions(1);

    while (true) {
        GetRangeResponse getRangeResponse = client.getRange(new GetRangeRequest(rangeRowQueryCriteria));
        for (Row row : getRangeResponse.getRows()) {
            // Extract the data table primary key from the index row.
            PrimaryKey curIndexPrimaryKey = row.getPrimaryKey();
            PrimaryKeyColumn pk1 = curIndexPrimaryKey.getPrimaryKeyColumn(PRIMARY_KEY_NAME_1);
            PrimaryKeyColumn pk2 = curIndexPrimaryKey.getPrimaryKeyColumn(PRIMARY_KEY_NAME_2);
            PrimaryKeyBuilder mainTablePKBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
            mainTablePKBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_1, pk1.getValue());
            mainTablePKBuilder.addPrimaryKeyColumn(PRIMARY_KEY_NAME_2, pk2.getValue());
            PrimaryKey mainTablePK = mainTablePKBuilder.build();

            // Fetch the required columns from the data table.
            SingleRowQueryCriteria criteria = new SingleRowQueryCriteria("TABLE_NAME", mainTablePK);
            criteria.addColumnsToGet(DEFINED_COL_NAME3);
            criteria.setMaxVersions(1);
            GetRowResponse getRowResponse = client.getRow(new GetRowRequest(criteria));
            Row mainTableRow = getRowResponse.getRow();
            System.out.println(row);
        }
        if (getRangeResponse.getNextStartPrimaryKey() != null) {
            rangeRowQueryCriteria.setInclusiveStartPrimaryKey(getRangeResponse.getNextStartPrimaryKey());
        } else {
            break;
        }
    }
}

Appendix: Delete an index table

Delete an index table when it is no longer needed.

The examples below use Tablestore SDK for Java. The following SDKs are also supported: Go, Python, Node.js, .NET, and PHP.

private static void deleteIndex(SyncClient client) {
    DeleteIndexRequest request = new DeleteIndexRequest("<TABLE_NAME>", "<INDEX_NAME>");
    client.deleteIndex(request);
}

FAQ

References

Use secondary indexes in the Tablestore console or the Tablestore CLI. For more information, see Use secondary indexes in the Tablestore console and Secondary index.
For more flexible query options — including full-text search, Boolean query, prefix query, and fuzzy query — use the search index feature. For more information, see Overview.