Using a secondary index
Use CQL to create a secondary index on a column after defining a table.
Secondary Indexes
Create indexes on one or more columns after defining a table. Secondary indexes created with Storage-Attached Indexing (SAI) are used to query a table using a column other than the table's partition key.
Attention: Do not add an SAI index and a DSE Search index on the same
table. See CREATE CUSTOM INDEX for details.
Procedure
-
The table
rank_by_year_and_name
stores the rank of cyclists for races.CREATE TABLE IF NOT EXISTS cycling.rank_by_year_and_name ( race_year int, race_name text, cyclist_name text, rank int, PRIMARY KEY ((race_year, race_name), rank) );
-
Both
race_year
andrace_name
must be specified in a query because those columns comprise the partition key.SELECT * FROM cycling.rank_by_year_and_name WHERE race_year = 2014 AND race_name = 'Tour of Japan - Stage 4 - Minami > Shinshu';
race_year | race_name | rank | cyclist_name -----------+--------------------------------------------+------+---------------------- 2014 | Tour of Japan - Stage 4 - Minami > Shinshu | 1 | Daniel MARTIN 2014 | Tour of Japan - Stage 4 - Minami > Shinshu | 2 | Johan Esteban CHAVES 2014 | Tour of Japan - Stage 4 - Minami > Shinshu | 3 | Benjamin PRADES (3 rows)
-
A logical query to try is a listing of the rankings for a particular year.
Because the table has a composite partition key with
race_year
andrace_name
, the following query fails because only therace_year
column is specified in theWHERE
clause.SELECT * FROM cycling.rank_by_year_and_name WHERE race_year = 2014;
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"
-
However, this next example uses Storage-Attached Indexing (SAI) and the
CREATE CUSTOM INDEX
command. An SAI index is created for the race year, and the subsequent query succeeds.CREATE CUSTOM INDEX race_year_idx ON cycling.rank_by_year_and_name (race_year) USING 'StorageAttachedIndex';
SELECT * FROM cycling.rank_by_year_and_name WHERE race_year = 2014;
race_year | race_name | rank | cyclist_name -----------+--------------------------------------------+------+---------------------- 2014 | 4th Tour of Beijing | 1 | Phillippe GILBERT 2014 | 4th Tour of Beijing | 2 | Daniel MARTIN 2014 | 4th Tour of Beijing | 3 | Johan Esteban CHAVES 2014 | Tour of Japan - Stage 4 - Minami > Shinshu | 1 | Daniel MARTIN 2014 | Tour of Japan - Stage 4 - Minami > Shinshu | 2 | Johan Esteban CHAVES 2014 | Tour of Japan - Stage 4 - Minami > Shinshu | 3 | Benjamin PRADES (6 rows)