Tablestore:Search index

Index type

Description

Scenario

Primary key of a data table

A data table is similar to a large map. Data tables support queries based only on primary key columns.

Primary key-based queries are suitable for scenarios in which the values of all primary key columns or the primary key prefix can be determined.

Secondary index

You can create one or more index tables for a data table and perform queries by using the primary key columns of the index tables. The primary key columns of an index table consist of all primary key columns and a specific number of predefined columns of the data table for which the index table is created.

Secondary indexes are suitable for scenarios in which the columns to be queried can be determined, the number of columns to be queried is small, and the values of all primary key columns or the primary key prefix can be determined.

Search index

Search indexes use inverted indexes, Bkd-trees, and column stores for various query scenarios.

Search indexes are suitable for all query and analysis scenarios in which queries based on the primary key columns and secondary indexes of data tables cannot meet your business requirements. For example, you can use search indexes to perform queries based on non-primary key columns, Boolean queries, relational queries, full-text search, geo queries, fuzzy queries, nested queries, exists queries, and aggregation operations.

Feature

Description

References

Query based on primary key columns and non-primary key columns

You can use a search index to query data based on a random column. This feature meets query requirements in most scenarios.

If queries based on primary key columns or primary key prefix do not meet your business requirements, you can create a search index and add columns based on which you want to query data to the search index. This way, you can use the search index to query data based on the columns.

Documentation for query methods provided by search indexes, such as Basic query

Boolean query

You can combine multiple fields to query data in an efficient manner. This feature is suitable for scenarios, such as order systems, log analysis, and user profiles in which you want to filter data based on multiple conditions.

For example, a data table in the order scenario may contain dozens of fields. To query data based on various combinations of fields in a relational database, you may need to create hundreds of indexes. In addition, if a combination is not considered in advance and the corresponding index is not created, you cannot query data in an efficient manner.

However, if you use Tablestore, you only need to create a search index and add the fields based on which you may want to query data to the search index. When you use the search index to query data, you can specify conditions based on a combination of the fields in the search index based on your business requirements. Search indexes also support logical operators such as AND, OR, and NOT.

Boolean query

Geo query

As mobile devices gain popularity, geographical location data becomes increasingly important. Geographical location data is used in various apps such as social media apps, food delivery apps, sports apps, and Internet of Vehicles (IoV) apps. Queries based on geographical location data are required.

Search indexes support the following query features based on geographical location data:

• Near: queries points within a specific radius based on a central point, such as the People Nearby feature in WeChat.

• Within: queries points within a specific rectangular or polygonal area.

Search indexes of Tablestore allow you to use these features to query geographical location data without the need to use other database services or search engines.

• Geo-distance query

• Geo-bounding box query

• Geo-polygon query

Full-text search

You can query data that contains a specific string. This feature is suitable for scenarios, such as big data analytics, content search, knowledge management, social media analysis, log analysis, intelligent Q&A systems, compliance review, and intelligent recommendation.

Search indexes can tokenize data to support full-text search. You can use search indexes to sort query results based on only the BM25-based keyword relevance score. If you want to sort query results based on complex relevance scores, we recommend that you use search systems.

Search indexes support the following tokenization methods: single-word tokenization, delimiter tokenization, minimum semantic unit-based tokenization, maximum semantic unit-based tokenization, and fuzzy tokenization. You can select tokenization methods based on your requirements. If you want to highlight the query string in the query results, you can use the highlight feature.

• Match query

• Match phrase query

• Tokenization

• Highlight

KNN vector query

Search indexes provide the KNN vector query feature. You can find the most similar data items in large-scale datasets by using vectors to perform approximate nearest neighbor searches. This feature is suitable for scenarios such as retrieval augmented generation (RAG), recommend systems, similarity detection for images, videos, and voices, and Natural Language Processing (NLP).

KNN vector query

Fuzzy query

Search indexes of Tablestore support the wildcard query, prefix query, and suffix query features to meet your fuzzy query requirements in different scenarios.

• Wildcard query: This feature is similar to the LIKE operator in traditional relational databases. You can specify a string that contains asterisks (*) and question marks (?) to perform wildcard queries.

  If you perform a wildcard query to search for the *word* string, you can use a tokenization-based wildcard query (fuzzy tokenization in combination with match phrase query) to ensure better query performance.

• Prefix query: You can specify a prefix to query data that matches the specified prefix. For example, in a query based on the prefix apple, results such as apple6s and applexr are returned. This feature is suitable for all natural languages.

• Suffix query: You can specify a suffix to query data that matches the specified suffix. For example, you can query mobile phone numbers that end with 1234.

• Wildcard query

• Tokenization-based wildcard query

• Prefix query

• Suffix query

Exists query

You can query whether a column of a row exists. An exists query is also called a NULL query or a NULL-value query. This feature is suitable for scenarios such as data integrity check and data cleansing.

Exists query

Nested query

In addition to a flat structure, the data produced by online applications often has a complex and multilayered structure, such as tagged images. For example, a database stores a large number of images, and each image has multiple elements such as houses, cars, and people. Each element in an image has a unique weight score. The score is evaluated based on the size and position of an element in an image. Therefore, each image has multiple tags. Each tag has a name and a weight score.

If you want to filter images based on tag conditions, you can use the nested query feature. Image tags are stored in the JSON format. Example:

{
 "tags": [
   {
      "name": "car",
      "score": 0.78
   },
   {
      "name": "tree",
      "score": 0.24
   }
 ]
}

You can use the nested query feature to handle storage and query requirements of data that has multilayered logical relationships in an efficient manner, facilitating the modeling of complex data.

For Nested fields in complex data structures such as JSON, you can also enable the highlight feature to precisely locate the required information.

• Array and Nested data types

• Nested query

Collapse (distinct)

Search indexes support the collapse (distinct) feature to improve the diversity of query results. This feature limits the maximum number of occurrences of an attribute in the query results of a query request. This avoids excessive query results that have the same attribute. For example, in e-commerce scenarios, if you search for laptop, the first page may display laptops of the same brand. The results are not user-friendly. In this case, you can use the collapse (distinct) feature to ensure that the first page displays laptops from distinct brands.

Collapse (distinct)

Sorting

In Tablestore, table data is automatically sorted in alphabetical order of the primary key. To sort data based on other fields, you must use the sorting feature of search indexes.

Search indexes support sorting in ascending order or descending order based on one or more conditions. The sorting is globally performed on all data in a search index. By default, the returned results of a search index are sorted based on the primary key in the data table.

• Perform sorting and paging

• Documentation for query methods provided by search indexes, such as Basic query

Match all query

When you use a search index to query data, you can specify that Tablestore returns the total number of rows that meet the query conditions. This feature is suitable for scenarios such as data verification and data-driven operations.

• If you do not specify query conditions for the search index, Tablestore returns the total number of rows for which you have created indexes.

• If you stop writing new data to a table and create indexes for all data in the table, the total number of rows returned by Tablestore is the total number of rows in the data table.

• Match all query

• Documentation for query methods provided by search indexes, such as Basic query

Aggregation

Search indexes allow you to perform aggregation operations to obtain the maximum value, minimum value, average value, sum, count and distinct count of rows, percentiles, and histogram statistics. You can also use search indexes to group results. This allows you to perform lightweight statistical analysis.

Aggregation

Item

Table partitioning by time

Principle

Partition a data table based on fixed periods of time, such as daily, weekly, monthly, or annually. Then, create a search index for each partitioned table. This way, you can retain tables for the specified periods of time based on your business requirements.

For example, to retain data for a six-month period, you can store the data for each month in a data table. Label the data tables in sequence from table_1 to table_6 and create a search index for each data table. Make sure that each data table and its search index exclusively store data for their respective month. To implement the TTL feature, you need to only delete data tables and their associated search indexes that are retained for longer than six months.

When you query data by using a search index, you need to only query a specific data table if the data table contains all the data that falls within your desired time range. If the data that falls within your desired time range spans multiple data tables, you need to query all these data tables and merge the query results.

Rule

The size of a single search index can be up to 50 billion rows. To ensure the optimal query performance, we recommend that you limit the size of a single search index to 20 billion rows or fewer.

Benefits

• You can adjust the data storage duration based on the number of data tables retained.

• Query performance is directly proportional to data volumes. After a data table is partitioned into multiple data tables, the data volume of each data table has an upper limit. This ensures better query performance and avoids high query latency or timeouts.

Category

Operation

Description

Index management

CreateSearchIndex

Creates a search index.

UpdateSearchIndex

Updates the configurations of a search index, including the TTL and schema configurations.

DescribeSearchIndex

Queries the details about a search index.

ListSearchIndex

Queries the list of search indexes.

DeleteSearchIndex

Deletes a search index.

Data query

Search

Supports all features of search indexes. You can call the Search operation to query data by using all supported query methods and analyze data by performing sorting and aggregation operations. The query results are returned based on the specified order.

• Query methods: query based on non-primary key columns, exists query, fuzzy query, Boolean query, nested query, geo query, full-text search, and KNN vector query

• Collapse (distinct)

• Sorting

• Aggregation

• Match all query

ParallelScan

Exports data in parallel. You can call the ParallelScan operation to query data by using all supported query methods. However, to ensure faster retrieval of query results, the ParallelScan operation does not support analysis features such as sorting and aggregation.

The ParallelScan operation offers superior query performance compared to the Search operation. When the ParallelScan operation processes a single query request that includes a parallel scan task, its throughput is five times that of the Search operation.

• Query methods: query based on non-primary key columns, exists query, fuzzy query, Boolean query, nested query, geo query, and full-text search

• Multiple parallel scan tasks included in one ParallelScan request

When you call this operation, you must call the ComputeSplits operation to query the maximum number of parallel scan tasks for a single ParallelScan request.