Work with table indexes
|
This Astra DB Serverless feature is currently in public preview. Development is ongoing, and the features and functionality are subject to change. Astra DB Serverless, and the use of such, is subject to the DataStax Preview Terms. The Data API tables commands are available through HTTP and the clients. If you use a client, tables commands are available only in client versions 2.0-preview or later. For more information, see Data API client upgrade guide. |
Table indexes allow you search on columns outside the table’s primary key.
Creating a vector index on a vector column allows you to perform vector searches on the table.
Creating regular indexes on non-vector columns allows you to run normal queries against those columns.
Querying a non-indexed column either fails or triggers allow filtering, which is the least efficient way to query a table. Allow filtering can be slow and expensive, especially on large tables.
In contrast, querying on the primary key is the most efficient way to search. Indexes are not as performant as primary key queries, but they are much more performant than allow filtering.
You can use the Data API to manage indexes for fixed schema tables. For more information about creating and managing tables with the Data API, see Work with tables. For information about indexing in dynamic schema collections, see The indexing option.
Index options for text and ascii types
When creating an index on a text or ascii column, you can specify the following options:
-
ASCII: Whether to convert non-ASCII characters to their US-ASCII equivalent before indexing. The default is false.
This option can improve search performance by limiting the range of allowed ASCII characters. This option is best for ASCII-only data or ASCII-only applications where you can safely ignore non-ASCII characters. It is not recommended for multilingual data or applications that must support non-ASCII characters.
If true, then alphabetic, numeric, and symbolic characters that are not in the Basic Latin Unicode block (the first 127 ASCII characters) are converted to the ASCII equivalent, if one exists. For example,
àis indexed asa, and searches usingacan matchà. Characters with no equivalent are ignored. -
Normalize: Whether to normalize Unicode characters and diacritics before indexing. The default is false.
This option can improve search consistency and inclusivity by normalizing Unicode characters, such as accented characters and diacritical marks that have multiple Unicode representations. This option is best for data sets that can contain multiple Unicode versions of the same character, such as multilingual data or non-standardized user input.
If true, multiple versions of a Unicode character are normalized to a single version while retaining all marks and symbols in the index. For example,
Å (U+212B)normalizes toÅ (U+00C5). -
Case sensitive: Whether the index is case sensitive. The default is true.
This option enforces case sensitivity in searches. It is best for data sets where you want to enforce exact matches based on capitalization.
If true, indexing and searches are case sensitive and honor capitalization. If false, indexing and searches are case insensitive and ignore capitalization.
