Using a secondary index

Use CQL to create a secondary index on a column after defining a table.

• 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)