Secondary Indexes

What is a secondary index?
Secondary indexes (also called global secondary indexes) are indexes created on columns that are not part of the partition key or not entirely part of it. Each secondary index indexes one specific column.) They are created for one main purpose: to allow efficient querying by a column that is not a key. CQL tables have strict schemas that define which columns form a primary key, and fundamentally you should use these keys to extract data from the database. But, in practice, you may want to occasionally query by a different, non primary key column, or several of them. How do you achieve that? One of the ways is to create a secondary index on that column.

Under the hood, secondary indexes are implemented on top of materialized views. It implies that there’s a storage overhead for creating an index – it will use another table to store the data it needs. Indexes can be global or local, as explained in just a bit.

Usage
Create table:
CREATE TABLE buildings (
name TEXT,
city TEXT,
height INT,
PRIMARY KEY (name)
);

Create secondary index:
CREATE INDEX buildings_by_city ON buildings (city);

Query the table using the newly created index:

SELECT * FROM buildings WHERE city = ‘New York City’;

Drop the index:
DROP INDEX buildings_by_city;

When can you use secondary indexes?
You can use secondary indexes when you want to query by a column that is not part of the partition key.