Secondary indexes

Secondary indexes are indexes on columns that are not part of a primary key. Secondary indexes allow efficient querying on non-primary key columns. This could be the case in some scenarios, for example, the column family for cars:

CREATE TABLE cars (
brand text,
model text,
variant text,
body_type text,
colors set<text>,
mileage decimal,
    PRIMARY KEY (brand, model), variant)
)

We might want to be able to search for cars of a specific body type, for example all cars with the hatchback body type. In order to achieve this, we could create a secondary index on the body_type column as follows:

CREATE INDEX cars_body_type ON cars(body_type)

Now, we could search for all hatchback cars of all brands, as follows:

SELECT * FROM cars WHERE body_type = 'hatchback';

brand  | model | variant                      | body_type | colors                            | mileage
--------+-------+------------------------------+-----------+-----------------------------------+---------
Maruti...