Using multiple indexes

How to use multiple secondary indexes.

Indexes can be created on multiple columns and used in queries. The general rule about cardinality applies to all columns indexed. In a production environment, certain columns might not be good choices, depending on their cardinality.

Procedure

The table cycling.alt_stats can yield the statistics about cyclists.

 CREATE TABLE cycling.cyclist_alt_stats ( id UUID PRIMARY KEY, lastname text, birthday timestamp, nationality text, weight text, height text );

Create indexes on the columns birthday and nationality.

CREATE INDEX birthday_idx ON cycling.cyclist_alt_stats ( birthday );
CREATE INDEX nationality_idx ON cycling.cyclist_alt_stats ( nationality );

Query for all the cyclists with a certain birthday from a particular country.

SELECT * FROM cycling.cyclist_alt_stats WHERE birthday = '1982-01-29' AND nationality = 'Russia';

The indexes have been created on appropriate low-cardinality columns, but the query still fails. Why? The answer lies with the partition key, which has not been defined. When you attempt a potentially expensive query, such as searching a range of rows, the database requires the ALLOW FILTERING directive. The error is not due to multiple indexes, but the lack of a partition key definition in the query.
```
SELECT * FROM cycling.cyclist_alt_stats WHERE birthday = '1990-05-27' AND nationality = 'Portugal' ALLOW FILTERING
```