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. 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 IF NOT EXISTS in a CREATE [CUSTOM] INDEX command, and an index with the same name already exists on a different table in the keyspace, the command will not succeed. However, no error will indicate that an index with the same name already exists on another table. An error will be thrown only if the index is created without the IF NOT EXISTS clause.


To create a simple 2i index:

CREATE INDEX id_idx ON cycling.cyclist_alt_stats (id);

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2024 DataStax | Privacy policy | Terms of use

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: +1 (650) 389-6000,