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 and race_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 and race_name, the following query fails because only the race_year column is specified in the WHERE 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' 
    WITH OPTIONS = { 'case_sensitive': 'false' };
    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)