Search index string types - Tablestore - Alibaba Cloud Documentation Center

This topic describes three string-related data types: Keyword, FuzzyKeyword, and Text. It also explains how to choose the appropriate string type for your application.

Background information

The String type is the most common data type in data systems and is used in almost all business scenarios. In traditional relational databases, the String type is classified into various types, such as fixed-length and variable-length strings.

In Tablestore, the main string type is String. It is similar to the String type in programming languages such as C++ and Java. To support more features, search indexes classify strings into three specific types: Keyword, which is similar to a standard string; FuzzyKeyword, which is used for fuzzy queries; and Text, which supports tokenization for full-text search.

This topic describes the relationships and differences between these three string types. It also explains how to select the correct type for your business scenario.

Data type mappings between tables and search indexes

The String type in tables can be mapped to the Keyword, FuzzyKeyword, or Text type in search indexes. You can also use the virtual column feature to map the Integer or Double type in tables to the Keyword, FuzzyKeyword, or Text type in search indexes.

Data type in tables	Method	Data type in search indexes
String	Direct usage	Keyword
		FuzzyKeyword
		Text
Integer	Virtual column	Keyword
		FuzzyKeyword
		Text
Double	Virtual column	Keyword
		FuzzyKeyword
		Text

Data types

Keyword

The Keyword type is the most commonly used string type. It is similar to the string type in databases and programming languages such as C++ and Java.

The Keyword type supports term query, range query, wildcard query, prefix query, exists query (null value query), sorting, and aggregate operations such as GroupBy. For medium-to-large datasets with over a million entries, the performance of wildcard and prefix queries degrades as the dataset size increases.

You can use the Keyword type if you do not need fuzzy query or full-text search features. The Keyword type is required for features such as sorting and statistical aggregation.

Text

The Text type is similar to the text type in search engines and is used for full-text search. It supports multiple tokenization methods. The system first chunks a string into a series of tokens and then stores them individually.

The Text type offers various tokenization methods. Some methods perform simple chunking by word or character. Others perform chunking based on semantics. Different methods are suitable for different business scenarios. For more information, see Tokenization.

You can use the Text type for full-text search features.

FuzzyKeyword

The FuzzyKeyword type is a string type that supports high-performance wildcard, prefix, and suffix queries. It maintains high query performance regardless of the dataset size.

You can use the FuzzyKeyword type when you require high-performance (millisecond-level) wildcard, prefix, or suffix queries.

To perform a suffix query, you can use the SuffixQuery with the FuzzyKeyword type. Alternatively, you can flip the data and use the lower-performance PrefixQuery with the Keyword type.

Note

FuzzyKeyword fields do not support sorting or statistical aggregation. If a FuzzyKeyword field also needs to be sorted or aggregated, you can use the virtual column feature to map the field to a Keyword type.
If a field requires multiple query features, such as term query, high-performance fuzzy query, and full-text search, you can use the virtual column feature. This lets you map one table column to three search index columns of the Keyword, Text, and FuzzyKeyword types respectively. For more information, see Virtual columns.

Differences between types

The Keyword, Text, and FuzzyKeyword types support different query features and have different maximum lengths. The following table describes the differences.

Note

A check mark (✓) indicates that the feature is supported. A cross mark (×) indicates that the feature is not supported.

Feature	Keyword	FuzzyKeyword	Text
Term query	✓	×	×
Terms query	✓	×	×
Range query	✓	×	×
Exists query (null value query)	✓	✓	✓
Sorting	✓	×	×
Statistical aggregation	✓	×	×
Full-text search: Tokenization	×	×	✓
Full-text search: Relevance score	×	×	✓
Full-text search: Highlighting and summary	×	×	✓
Full-text search: Match query	×	×	✓
Full-text search: Match phrase query	×	×	✓
Wildcard query	✓ (Lower performance on large datasets)	✓ (High performance)	×
Prefix query	✓ (Lower performance on large datasets)	✓ (High performance)	×
Suffix query	×	✓	×
Maximum length	4 KB	2 KB	2 MB