After you create a search index for a table, you can query fields only in the search index when you use the search index. You can create multiple search indexes for a table.

Optimal method to create search indexes

We recommend that you determine the number of search indexes that you want to create for a table based on your query requirements.

If you have a data table that contains the id, name, age, city, and sex fields, you can use one of the following methods to create a search index when you query data by name, age, or city:

  • Method 1: Create a search index for each field
    If you use this method, you must create the following search indexes: name_index, age_index, and city_index.
    • To query students by city, use city_index. To query students by age, use age_index.
    • However, this method does not work if you want to query students who are younger than 12 years old and live in City A.

    The implementation of the method is similar to that of the global secondary index feature. However, this method is not cost-effective. We recommend that you use Method 2 to create the search index.

  • Method 2: Create one search index for multiple fields
    In this method, a search index named student_index is created. The search index contains the following fields: name, age, and city.
    • To query students by city, query the city field in student_index. To query students by age, query the age field in student_index.
    • To query students who are younger than 12 years old and live in City A, query the age and city fields in student_index.

    This method makes full use of the advantages of search indexes and is more cost-effective. To create a search index, we recommend that you use this method.

Limits

  • Timeliness of search index creation

    After a search index is created, it takes a few seconds before you can use the search index. During this interval, you can only write data to the table, but you cannot query the metadata of the index or query data by using the index.

  • Limits on the number of search indexes

    For more information, see Search index limits.

Operations

You can call the CreateSearchIndex operation to create a search index.

Use Tablestore SDKs

You can use the following SDKs to create a search index:

Parameters

When you create a search index, you must specify tableName, indexName, and indexSchema. You must specify fieldSchemas, indexSetting, and indexSort in indexSchema. The following table describes the parameters.
Parameter Description
tableName The name of the table.
indexName The name of the search index.
fieldSchemas The list of field schemas. Each field schema contains the following parameters:
  • fieldName: required. This parameter specifies the name of the field in the search index. The value is a column name. Type: String.

    A field in a search index can be a primary key column or an attribute column.

  • fieldType: required. This parameter specifies the type of the field. Use FieldType.XXX to set the type. For more information, see Data types of column values.
  • array: optional. This parameter specifies whether the value is an array. Type: Boolean.

    If you set this parameter to true, the column stores data as an array. Data written to the column must be a JSON array. Example: ["a","b","c"].

    Nested values are considered as an array. If you set fieldType to Nested, skip the array parameter.

  • index: optional. This parameter specifies whether to create an index for the column. Type: Boolean.

    By default, this parameter is set to true, and Tablestore creates an inverted index or spatial index for the column. If this parameter is set to false, Tablestore does not create indexes for the column.

  • analyzer: optional. This parameter specifies the type of analyzer to use. If the fieldType is set to Text, you can set this parameter. If you do not specify this parameter, single-word tokenization is used. For more information about tokenization, see Tokenization.
  • enableSortAndAgg: optional. This parameter specifies whether to enable the sorting and aggregation features. Type: Boolean.
    Before you enable the sorting feature, you need to set enableSortAndAgg to true for a field. For more information about sorting, see Sorting and pagination.
    Notice Fields of the Nested type do not support sorting and aggregation, but subcolumns of the Nested type support sorting and aggregation.
  • store: optional. This parameter specifies whether to store the value of the field in the search index. Type: Boolean.

    If you set the value to true, you can read the value of the field directly from the search index without querying the entire table. This improves query performance.

  • isVirtualField: optional. This parameter specifies whether the field is a virtual column. Type: Boolean. This parameter is required only when you use virtual columns. For more information about virtual columns, see Virtual columns.
  • sourceFieldName: optional. This parameter specifies the name of the source field to which the virtual column is mapped in the table. Type: String. This parameter is required when isVirtualField is set to true.
indexSetting The settings of the search index, including routingFields.

routingFields: optional. This parameter specifies custom routing fields. You can specify some primary key columns as routing fields. Tablestore distributes data that is written to a search index to different partitions based on the specified routing fields. The data with the same routing field values is distributed to the same partition.

indexSort The presorting settings of the search index, including sorters. If indexSort is left empty, data is sorted by primary key.
Note You can skip the presorting settings for search indexes that contain the Nested field type.
sorters: required. This parameter specifies the presorting method for the search index. PrimaryKeySort and FieldSort are supported. For more information, see Sorting and pagination.
  • PrimaryKeySort: Data is sorted by primary key. You can configure the following parameter for PrimaryKeySort:

    order: the sort order. Data can be sorted in ascending or descending order. Default value: SortOrder.ASC.

  • FieldSort: Data is sorted by field value. You can configure the following parameters for FieldSort:

    You can presort field values only when a search index is created and the sorting and aggregation features are enabled for fields in the search index.

    • fieldName: the name of the field to sort.
    • order: the sort order. Data can be sorted in ascending or descending order. Default value: SortOrder.ASC.
    • mode: the sorting method used when the field has multiple values.

Examples

  • The following code provides an example on how to create a search index.
    Create a search index that consists of the Col_Keyword and Col_Long columns. Set the type of data in Col_Keyword to String and Col_Long to Long.
    
    private static void createSearchIndex(SyncClient client) {
        CreateSearchIndexRequest request = new CreateSearchIndexRequest();
        request.setTableName(TABLE_NAME); // Set the name of the table. 
        request.setIndexName(INDEX_NAME); // Set the name of the search index. 
        IndexSchema indexSchema = new IndexSchema();
        indexSchema.setFieldSchemas(Arrays.asList(
                new FieldSchema("Col_Keyword", FieldType.KEYWORD) // Set the name and type of the field. 
                        .setIndex(true) // Enable the indexing feature. 
                        .setEnableSortAndAgg(true) // Enable the sorting and aggregation features. 
                        .setStore(true), // Specify the value of the field to be stored by Tablestore in the search index. 
                new FieldSchema("Col_Long", FieldType.LONG)
                        .setIndex(true)
                        .setEnableSortAndAgg(true)
                        .setStore(true)));
        request.setIndexSchema(indexSchema);
        client.createSearchIndex(request); // Call the client to create the search index. 
    }
  • The following code provides an example on how to presort data by using IndexSort when you create a search index.
    private static void createSearchIndexWithIndexSort(SyncClient client) {
        CreateSearchIndexRequest request = new CreateSearchIndexRequest();
        request.setTableName(TABLE_NAME);
        request.setIndexName(INDEX_NAME);
        IndexSchema indexSchema = new IndexSchema();
        indexSchema.setFieldSchemas(Arrays.asList(
                new FieldSchema("Col_Keyword", FieldType.KEYWORD).setIndex(true).setEnableSortAndAgg(true).setStore(true),
                new FieldSchema("Col_Long", FieldType.LONG).setIndex(true).setEnableSortAndAgg(true).setStore(true),
                new FieldSchema("Col_Text", FieldType.TEXT).setIndex(true).setStore(true),
                new FieldSchema("Timestamp", FieldType.LONG).setIndex(true).setEnableSortAndAgg(true).setStore(true)));
        // Presort data by the Timestamp column. You must create a search index and enable enableSortAndAgg for sorting and aggregation for the Timestamp column. 
        indexSchema.setIndexSort(new Sort(
                Arrays.<Sort.Sorter>asList(new FieldSort("Timestamp", SortOrder.ASC))));
        request.setIndexSchema(indexSchema);
        client.createSearchIndex(request);
    }
  • The following code provides an example on how to specify a virtual column when you create a search index.
    A search index is created. The search index contains columns Col_Keyword and Col_Long. Each of the column has a virtual column. The virtual column of the Col_Keyword column is Col_Keyword_Virtual_Long and that of the Col_Long column is Col_Long_Virtual_Keyword. The Col_Keyword_Virtual_Long column is mapped to the Col_Keyword column in the table, and the Col_Long_Virtual_Keyword column is mapped to the Col_Long column in the table.
    private static void createSearchIndex(SyncClient client) {
        CreateSearchIndexRequest request = new CreateSearchIndexRequest();
        request.setTableName("tableName"); // Set the name of the table. 
        request.setIndexName("indexName"); // Set the name of the search index. 
        IndexSchema indexSchema = new IndexSchema();
        indexSchema.setFieldSchemas(Arrays.asList(
            new FieldSchema("Col_Keyword", FieldType.KEYWORD) // Set the name and type of the field. 
                .setIndex(true) // Enable the indexing feature. 
                .setEnableSortAndAgg(true) // Enable the sorting and aggregation features. 
                .setStore(true),
            new FieldSchema("Col_Keyword_Virtual_Long", FieldType.LONG) // Set the name and type of the field. 
                .setIndex(true)
                .setEnableSortAndAgg(true)
                .setStore(true)
                .setVirtualField(true) // Specify whether the field is a virtual column. 
                .setSourceFieldName("Col_Keyword"), // The name of the source field to which the virtual column is mapped in the table. 
            new FieldSchema("Col_Long", FieldType.LONG)
                .setIndex(true)
                .setEnableSortAndAgg(true)
                .setStore(true),
            new FieldSchema("Col_Long_Virtual_Keyword", FieldType.KEYWORD)
                .setIndex(true)
                .setEnableSortAndAgg(true)
                .setStore(true)
                .setVirtualField(true)
                .setSourceFieldName("Col_Long")));
        request.setIndexSchema(indexSchema);
        client.createSearchIndex(request); // Call the client to create the search index. 
    }