CREATE INDEX
Defines a new index for a single column of a table.
CQL supports creating an index on most columns, including the partition and cluster columns of a PRIMARY KEY
, collections, and static columns.
The one exception is that an index cannot be defined based on a single-column partition key.
All column date types except the following are supported for indexes:
-
counter
-
Geospatial types:
PointType
,LineStringType
,PolygonType
-
Non-frozen user-defined type (UDT)
-
Frozen user-defined type (UDT)
For maps, you can create indexes using the key, value, or entry (a key:value pair). You can create multiple secondary indexes on the same database table, with each index based on any column in the table.
You can define an index on any single column in a table’s composite partition key (a partition key comprised of multiple columns). If you need to query based on one of those columns, an index is a helpful option. You can define an index on each column in a composite partition key, if needed.
Defining one or more indexes based on any column in a database table (with the rules noted above) subsequently enables performant queries that use the indexed column to filter results.
Syntax
CREATE [CUSTOM] INDEX [ IF NOT EXISTS ] [ <index_name> ] ON [<keyspace_name>.]<table_name> ([ KEYS | VALUES | ENTRIES | FULL] <column_name>) USING <index_type> [ WITH OPTIONS = { <option_map> } ] ;
Syntax legend
Syntax conventions | Description |
---|---|
UPPERCASE |
Literal keyword. |
Lowercase |
Not literal. |
|
Variable value. Replace with a user-defined value. |
|
Optional.
Square brackets ( |
|
Group.
Parentheses ( |
|
Or.
A vertical bar ( |
|
Repeatable.
An ellipsis ( |
|
Single quotation ( |
|
Map collection.
Braces ( |
Set, list, map, or tuple.
Angle brackets ( |
|
|
End CQL statement.
A semicolon ( |
|
Separate the command line options from the command arguments with two hyphens ( |
|
Search CQL only: Single quotation marks ( |
|
Search CQL only: Identify the entity and literal value to overwrite the XML element in the schema and solrConfig files. |
Parameters
- index_name
-
Optional identifier for index. If no name is specified, the default is
<table_name>_<column_name>_idx
. Enclose in quotes to use special characters or to preserve capitalization.Index names are unique per keyspace because indexes are created at the keyspace level and not at the table level.
The index name must be a unique identifier for the index for each table within a keyspace. Enclose in quotes to use special characters or preserve capitalization. If you do not specify an index name, CQL generates one for you, with the pattern <table_name>_<column_name>_idx. This requirement is true for all indexes.
If you use IF NOT EXISTS
in a CREATE [CUSTOM] INDEX
command, the command fails silently if an index with the same name already exists in the keyspace.
If you want the command to return an error when an index with the same name already exists, don’t use IF NOT EXISTS
.
- keyspace_name
-
Optional name of the keyspace that contains the table to index. If no name is specified, the current keyspace is used.
- table_name
-
The name of the table on which the index is being defined.
- column_name
-
The name of the column to index.
If used with a map, the column name is the map name. For maps, you can create indexes using the key, value, entry (a key:value pair), or full content of the collection.
SAI allows only alphanumeric characters and underscores in names. SAI returns
InvalidRequestException
if you try to define an index on a column name that contains other characters, and does not create the index. - map_name
-
Used with collections, identifier of the
map_name
specified inCREATE TABLE
…map(<map_name>)
. The regular column syntax applies for collection typeslist
andset
. - CREATE INDEX | CREATE CUSTOM INDEX, USING <index_type>
-
See Index type options.
- WITH OPTIONS = { <option_map> }
-
See SAI options .
Index type options
Option | Description |
---|---|
|
A feature that is not available in all CQL distributions, but is recommended for production use in the distributions where it is available. Define with |
SAI options
The options are specific to the index type and are not required.
The <option_map>
is a map of options and their values defined in JSON simple format.
Option | Description | Default |
---|---|---|
|
Vector search relies on computing the similarity between vectors to identify relevant matches.
The similarity function is used to compute the similarity between two vectors.
Choices are |
|
|
Configures the index for optimal performance for your vectors.
Choices are: |
|
|
Allows case-insensitive searches. |
|
|
Allow searches to be normalized for Unicode characters.
SAI supports Normalization Form C (NFC) Unicode.
When set to When implementations keep strings in a normalized form, equivalent strings have a unique binary representation. See Unicode Standard Annex #15, Unicode Normalization Forms. |
|
|
Allow searches to be limited to ASCII characters.
When set to |
|
|
The class that implements the analyzer.
Choices are |
|
|
The tokenizer for the index. Choices are standard, whitespace, ngram, keyword, simplepattern, stop, and mapping. |
|
|
The filters for the index. Choices are: porterstem, lowercase, synonym, and languages, including stem definitions like czechstem. |
None. |
|
The character filters for the index. Choices are htmlstrip, mapping, and patternreplace. |
None. |
Usage notes
If the column already contains data, it is indexed during the execution of this statement. After an index has been created, it is automatically updated when data in the column changes.
Indexing with the CREATE INDEX
command can impact performance.
Before creating an index, be aware of when and when not to create an index.
SAI notes
You can create multiple secondary indexes on the same database table, with each SAI index based on any column in the table. All column date types except the following are supported for SAI indexes:
-
counter
-
Geospatial types:
PointType
,LineStringType
,PolygonType
-
Non-frozen user-defined type (UDT)
You cannot define an SAI index based on the partition key when it’s comprised of only one column. If you attempt to create an SAI index in this case, SAI issues an error message.
Defining one or more SAI indexes based on any column in a database table (with the rules noted above) subsequently gives you the ability to run performant queries that use the indexed column to filter results.
Supported databases for SAI:
Supported databases:
-
Astra DB
-
HCD
-
DataStax Enterprise 6.8.3 and later
-
Cassandra 5.0 and later
SAI supports the following query operators for tables with SAI indexes:
-
Numerics:
=
,<
,>
,⇐
,>=
,AND
-
Strings:
=
,AND
-
Collections:
=
,CONTAINS
,CONTAINS KEY
In CQL queries using SAI indexes, the
|
SAI does not supports the following query operators for tables with SAI indexes:
-
Strings or Numerics:
LIKE
,OR
,IN
For more information about SAI, see the SAI section.
Examples
Detailed examples can be found for each type of indexing:
Index type | Example links |
---|---|
SAI |
|
2i |
|
SASI |