Create a secondary index (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 a column other than the table’s partition key.
In a production environment, certain columns might not be good choices, depending on their cardinality.
Do not add an storage-attached index (SAI) to the same table. See the difference between these index types in the overview.
Create simple 2i indexes on a table to see how indexing works.
Start by creating a table,
cycling.alt_stats, that yields statistics about cyclists:
CREATE TABLE IF NOT EXISTS cycling.cyclist_alt_stats ( id UUID PRIMARY KEY, lastname text, birthday date, nationality text, weight float, w_units text, height float, first_race date, last_race date );
Now create indexes on the columns
CREATE INDEX IF NOT EXISTS birthday_idx ON cycling.cyclist_alt_stats (birthday);
CREATE INDEX IF NOT EXISTS nationality_idx ON cycling.cyclist_alt_stats (nationality);
The following query attempts to retrieve the cyclists with a specified
The query returns an error:
SELECT * FROM cycling.cyclist_alt_stats WHERE birthday = '1982-01-29' AND nationality = 'Russia';
InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING"
The indexes have been created on appropriate low-cardinality columns, but the previous 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 = '1982-01-29' AND nationality = 'Russia' ALLOW FILTERING;
id | birthday | first_race | height | last_race | lastname | nationality | w_units | weight --------------------------------------+------------+------------+--------+------------+----------+-------------+---------+-------- e0953617-07eb-4c82-8f91-3b2757981625 | 1982-01-29 | 1998-02-15 | 1.78 | 2017-04-16 | BRUTT | Russia | kg | 68 (1 rows)
Thus, one of the difficulties of using 2is is illustrated. SAI is almost always a better option.