Create secondary index (2i)
|
SAI is almost always a better choice than 2i. |
Create indexes on one or more columns after defining a table. Secondary indexes created with 2i can be used to query a table using any column other than the table’s partition key. If a composite partition key is defined, a 2i index can be created on any of the partition key columns.
In a production environment, certain columns might not be good choices, depending on their cardinality.
|
If you decide to create an index, always create either a secondary index (2i) or a storage-attached index (SAI) on a table column, but not both. CQL will be harder to tune and understanding performance will be more difficult if you create both types of indexes on the same table. If you create either a secondary index (2i) or a storage-attached index (SAI) on a table, do not add a DSE Search index to the same table column. See the difference between these index types in the overview. |
To create an 2i index, define the table name and column name for the column to be indexed. Index name is optional. Index names are unique per keyspace. 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.
|
Since indexes are created at the keyspace level and not at the table level.
If you use the |
Prerequisites
To create a simple 2i index:
CREATE INDEX id_idx ON cycling.cyclist_alt_stats (id);