After you create a search index for a data table, you can query data in the data table based on the fields for which indexes are created in the search index. You can create multiple search indexes for a data table.
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 fieldIf 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 fieldsIn 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
- Tablestore SDK for Java: Create search indexes
- Tablestore SDK for Go: Create search indexes
- Tablestore SDK for Python: Create search indexes
- Tablestore SDK for Node.js: Create search indexes
- Tablestore SDK for .NET: Create search indexes
- Tablestore SDK for PHP: Create search indexes
Parameters
Parameter | Description |
---|---|
tableName | The name of the data table. |
indexName | The name of the search index. |
fieldSchemas | The list of field schemas. Each field schema contains the following parameters:
|
indexSetting | The settings of the search index, including routingFields. routingFields: This parameter is optional and specifies custom routing fields. You can specify some primary key columns as routing fields. Tablestore distributes data that is written to a search index across different partitions based on the specified routing fields. The data whose routing field values are the same is distributed to the same partition. |
indexSort | The presorting settings of the search index, including sorters. If no value is specified for the indexSort parameter, field values are sorted by primary key by default. Note You can skip the presorting settings for search indexes that contain fields of the Nested type. sorters: This parameter is optional and specifies the presorting method for the search index. PrimaryKeySort and FieldSort are supported. For more information, see Sorting and paging.
|
timeToLive | This parameter is optional and specifies the retention period of data in the search index. Unit: seconds. Default value: -1. If the retention period exceeds the TTL value, Tablestore automatically deletes expired data. The minimum timeToLive value is 86400, which is equal to one day. A value of -1 specifies that data never expires. For more information about how to manage the time to live (TTL) of search indexes, see TTL of search indexes. |
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 specifiedImportant 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. }