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

Parameters

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