Tablestore:Search index

Query method

Principle

Scenario

Primary key

A data table functions like a large map. You can only query data by primary key.

Suitable for scenarios where you know the full primary key or a key prefix.

Secondary index

Creates index tables whose primary key columns extend the query capability of the data table to different columns.

Suitable for scenarios where the query columns are predetermined, the column count is small, and you know the full primary key or a key prefix.

Search index

Uses structures such as inverted indexes, BKD trees, and column stores to provide rich query capabilities.

Suitable for all query and analysis scenarios beyond primary key and secondary index coverage: non-primary key column queries, boolean queries on any columns, relationship queries, full-text search, geo queries, fuzzy queries, nested queries, NULL value queries, and statistical aggregation.

Feature

Description

Document

Query on any column (including primary key and non-primary key columns)

Query data by any column. Suitable for most query scenarios.

If primary key or prefix queries cannot meet your needs, create a search index with the target fields and query by column values.

Any search index query, such as a basic query

Boolean query

Combine multiple fields for efficient filtering. Suitable for order systems, log analysis, and user personas.

In a relational database, a table with dozens of fields may require hundreds of indexes to cover all field combinations. Missing combinations result in inefficient queries.

With Tablestore, one search index covers all field combinations. Add the fields you might query to the index, then freely combine them using And, Or, and Not logic.

Boolean query

Geo query

Mobile devices have made geographic location data increasingly valuable. Applications for social networking, food delivery, sports, and the Internet of Vehicles (IoV) all require location-aware queries.

Search indexes support the following geo query features:

• Near: Query points within a specified distance from an origin. Example: the "People Nearby" feature in social media.

• Within: Query points within a rectangular or polygonal area.

If your application requires location-based queries, a Tablestore search index provides a one-stop solution without additional databases or search systems.

• Geo-distance query

• Geo-bounding box query

• Geo-polygon query

Full-text index

Find data containing a specified phrase. Suitable for big data analytics, content search, knowledge management, social media analysis, log analysis, AI chat systems, compliance reviews, and personalized recommendations.

Search indexes use tokenization for full-text search. They provide basic BM25 relevance but not custom relevance. For complex relevance search needs, use a dedicated search system; otherwise, a search index is sufficient.

Five tokenization types are available: single-word, delimiter, minimum semantic, maximum semantic, and fuzzy. To highlight keywords in results, use the summary and highlighting feature.

• Match query

• Match phrase query

• Tokenization

• Summary and highlighting

Vector search

Search indexes support vector search for efficient approximate nearest neighbor queries on large-scale datasets. Suitable for retrieval-augmented generation (RAG), recommendation systems, similarity detection (images, videos, and speech), and natural language processing.

AISearch

Fuzzy query

Search indexes provide wildcard, prefix, and suffix queries for fuzzy matching in different scenarios.

• Wildcard query: Similar to the `like` syntax in relational databases. Supports two wildcards: asterisk (*) and question mark (?).

  For *word* patterns, use a tokenization-based wildcard query (fuzzy tokenization combined with match phrase query) for better performance.

• Prefix query: Matches content by prefix. For example, querying apple matches apple6s and applexr. Supports Chinese, English, and other languages.

• Suffix query: Matches content by suffix. For example, you can query for all mobile phone numbers that end with 1234.

• Wildcard query

• Tokenization-based wildcard query

• Prefix query

• Suffix query

Column existence query (NULL query)

Check whether a column has a null value. Suitable for data integrity checks and data cleaning.

Column existence query

Nested query

Beyond flat structures, application data often has multi-level nested structures. For example, an image tagging system stores images with multiple entities (houses, cars, people), each with a different position, size, and weight (score). Each image maps to multiple tags, and each tag has a name and a weight score.

To filter images by tag conditions, use the nested type query. Image tags are stored in JSON format:

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

Nested type queries handle data with multi-level logical relationships, providing flexibility for complex data modeling.

For complex nested data structures (such as JSON), use the summary and highlighting feature to precisely locate required information.

• Array and nested types

• Nested query

Deduplication

Search indexes deduplicate query results to improve diversity. Deduplication limits how many times a specific attribute value appears in a single result set. For example, when searching for laptop on an e-commerce platform, deduplication prevents the first page from being dominated by a single brand.

Collapse (deduplication)

Sorting

Tablestore sorts data by primary key in alphabetical order by default. To sort by other fields, use the sorting feature of a search index.

Search indexes support ascending or descending order, single-condition sorting, and multi-condition sorting. All sorting is global. By default, search index results are sorted by the primary key in alphabetical order.

• Sorting and pagination

• Any search index query, such as a basic query

Total number of rows

When querying data with a search index, you can return the number of matching rows. This is useful for data validation and operations.

• An empty query condition matches all indexed data. The returned total equals the number of indexed rows in the data table.

• If data writing has stopped and all data is indexed, the total equals the number of rows in the data table.

• Match all query

• Any search index query, such as a basic query

Statistical aggregation

Search indexes provide common aggregation functions: Max, Min, Avg, Sum, Count, DistinctCount, GroupBy, Percentile, and Histogram. These meet basic statistical needs for lightweight analysis.

Statistical aggregation

Dimension

Table sharding by time

Principle

Shard tables by a fixed interval (day, week, month, or year). Create a search index for each table and retain data tables for the required duration.

For example, to retain data for six months, store each month's data in a separate table (table_1 through table_6) with its own search index. Each month, delete the table from six months ago.

When querying, if the time range falls within a single table, query that table only. If it spans multiple tables, query each and merge the results.

Rule

A single table (single index) must not exceed 50 billion rows. Query performance is optimal when the row count stays below 20 billion.

Advantages

• Control data retention duration by managing the number of retained tables.

• Query performance scales with data volume. Sharding caps each table's size, resulting in better performance and avoiding query timeouts.

Category

API

Description

Index management

CreateSearchIndex

Creates a search index.

UpdateSearchIndex

Updates the configuration of a search index, including its time to live (TTL) and index schema.

DescribeSearchIndex

Gets the detailed description of a search index.

ListSearchIndex

Lists the search indexes.

DeleteSearchIndex

Deletes a search index.

Data query

Search

Full-featured query API. Supports all search index features including query functions, sorting, and statistical aggregation. Results are returned in the specified order.

• Query functions: non-primary key column query, column existence query, fuzzy query, boolean query, nested query, geo query, full-text search, vector search

• Collapse (deduplication)

• Sorting

• Statistical aggregation

• Total number of rows

ParallelScan

Data-exporting API with parallel scan support. Includes all query functions but omits sorting and statistical aggregation. Returns all matched data at higher speed.

With a single concurrency, ParallelScan throughput is 5x that of the Search API.

• Query functions: non-primary key column query, column existence query, fuzzy query, boolean query, nested query, geo query, full-text search

• Supports multiple concurrent queries in a single request

When exporting data with multiple concurrent requests, use the ComputeSplits API to get the maximum concurrency for a single ParallelScan request.