Tablestore:Create search indexes

Optimal method to create search indexes

We recommend that you determine the number of search indexes that you want to create for a data 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 search indexes if you want to 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 search indexes.

• 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. We recommend that you use this method to create a search index.

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 period, you can only write data to the data table, but you cannot query the metadata of the index or query data by using the index.

• Quantity

  For more information, see Search index limits.

API operations

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

Use Tablestore SDKs

Parameters

Examples

• Create a search index
  The following sample code shows how to 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(tableName); // Specify the name of the data table. 
      request.setIndexName(indexName); // Specify the name of the search index. 
      IndexSchema indexSchema = new IndexSchema();
      indexSchema.setFieldSchemas(Arrays.asList(
              new FieldSchema("Col_Keyword", FieldType.KEYWORD) // Specify the name and type of the field. 
              new FieldSchema("Col_Long", FieldType.LONG)));
      request.setIndexSchema(indexSchema);
      client.createSearchIndex(request); // Call a client to create the search index. 
  }

• Create a search index with indexSort specified

  private static void createSearchIndexWithIndexSort(SyncClient client) {
      CreateSearchIndexRequest request = new CreateSearchIndexRequest();
      request.setTableName(tableName); // Specify the name of the data table. 
      request.setIndexName(indexName); // Specify the name of the search index. 
      IndexSchema indexSchema = new IndexSchema();
      indexSchema.setFieldSchemas(Arrays.asList(
              new FieldSchema("Col_Keyword", FieldType.KEYWORD),
              new FieldSchema("Col_Long", FieldType.LONG),
              new FieldSchema("Col_Text", FieldType.TEXT),
              new FieldSchema("Timestamp", FieldType.LONG)));
      // Presort data based on the Timestamp column. 
      indexSchema.setIndexSort(new Sort(
              Arrays.<Sort.Sorter>asList(new FieldSort("Timestamp", SortOrder.ASC))));
      request.setIndexSchema(indexSchema);
      client.createSearchIndex(request);
  }

• Create a search index with the TTL specified
  Important Make sure that updates to the data table are disabled.

  // Use Tablestore SDK for Java V5.12.0 or later to create a search index. 
  public void createIndexWithTTL(SyncClient client) {
      int days = 7;
      CreateSearchIndexRequest createRequest = new CreateSearchIndexRequest();
      createRequest.setTableName(tableName);
      createRequest.setIndexName(indexName);
      createRequest.setIndexSchema(indexSchema);
      // Specify the TTL for the search index. 
      createRequest.setTimeToLiveInDays(days);
      client.createSearchIndex(createRequest);
  }

• Create a search index with virtual columns specified
  The following sample code shows how to create a search index that contains columns Col_Keyword and Col_Long. Each of the columns 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 data table, and the Col_Long_Virtual_Keyword column is mapped to the Col_Long column in the data table.

  private static void createSearchIndex(SyncClient client) {
      CreateSearchIndexRequest request = new CreateSearchIndexRequest();
      request.setTableName(tableName); // Specify the name of the data table. 
      request.setIndexName(indexName); // Specify the name of the search index. 
      IndexSchema indexSchema = new IndexSchema();
      indexSchema.setFieldSchemas(Arrays.asList(
          new FieldSchema("Col_Keyword", FieldType.KEYWORD) // Specify the name and type of the field. 
          new FieldSchema("Col_Keyword_Virtual_Long", FieldType.LONG) // Specify the name and type of the field. 
              .setVirtualField(true) // Specify whether the field is a virtual column. 
              .setSourceFieldName("Col_Keyword"), // Specify the name of the source field to which the virtual column is mapped in the data table. 
          new FieldSchema("Col_Long", FieldType.LONG),
          new FieldSchema("Col_Long_Virtual_Keyword", FieldType.KEYWORD)
              .setVirtualField(true)
              .setSourceFieldName("Col_Long")));
      request.setIndexSchema(indexSchema);
      client.createSearchIndex(request); // Call a client to create the search index. 
  }