Working with secondary indexing (2i)

Prerequisites

• Keyspace created
• Table created

  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. cardinality.

  Create simple 2i

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

  birthday and nationality:

  CREATE INDEX IF NOT EXISTS birthday_idxON cycling.cyclist_alt_stats (birthday);CREATE INDEX IF NOT EXISTS nationality_idxON cycling.cyclist_alt_stats (nationality);

  birthday and nationality. The query returns an error:
• CQL
• Result

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

  InvalidRequest: Error from server: code=2200 [Invalid query]message="Cannot execute this query as it might involve datafiltering and thus may have unpredictable performance.If you want to execute this query despite the performanceunpredictability, use ALLOW FILTERING"

  The indexes have been created on appropriate low-cardinality columns, but the previous query still fails. Why? ALLOW FILTERING directive. The error is not due to multiple indexes, but the lack of a partition key definition in the query.
• CQL
• Result

  SELECT *FROM cycling.cyclist_alt_statsWHERE 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)

  SAI is almost always a better option.

  Create a 2i on a collection column

  Collections can be indexed and queried to find a collection containing a particular value. Sets and lists are indexed a bit differently from maps, given the key-value nature of maps. FULL to index the full content of a frozen collection. -

  CREATE INDEX IF NOT EXISTS teams_idxON cycling.cyclist_career_teams (teams);

  SELECT *FROM cycling.cyclist_career_teamsWHERE teams CONTAINS 'Rabobank-Liv Giant';

  id                                   | lastname | teams--------------------------------------+----------+---------------------------------------------------------------------------------------------------- 1c9ebc13-1eab-4ad5-be87-dce433216d40 |    BRAND | {'AA Drink - Leontien.nl', 'Leontien.nl', 'Rabobank-Liv Giant', 'Rabobank-Liv Woman Cycling Team'}(1 rows)

  -

  CREATE INDEX IF NOT EXISTS team_year_keys_idxON cycling.cyclist_teams ( KEYS (teams) );

  SELECT *FROM cycling.cyclist_teamsWHERE teams CONTAINS KEY 2015;

  id                                   | firstname | lastname   | teams--------------------------------------+-----------+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- cb07baad-eac8-4f65-b28a-bddc06a0de23 | Elizabeth | ARMITSTEAD | {2011: 'Team Garmin - Cervelo', 2012: 'AA Drink - Leontien.nl', 2013: 'Boels:Dolmans Cycling Team', 2014: 'Boels:Dolmans Cycling Team', 2015: 'Boels:Dolmans Cycling Team'} 5b6962dd-3f90-4c93-8f61-eabfa4a803e2 |  Marianne |        VOS |                                                                                                                                   {2015: 'Rabobank-Liv Woman Cycling Team'}(2 rows)

• ENTRIES is only valid for maps.

  CREATE TABLE IF NOT EXISTS cycling.birthday_list (  cyclist_name text PRIMARY KEY,  blist map<text, text>);

  CREATE INDEX IF NOT EXISTS blist_idxON cycling.birthday_list ( ENTRIES(blist) );

  SELECT *FROM cycling.birthday_listWHERE blist[ 'age' ] = '23';

  cyclist_name     | blist------------------+----------------------------------------------------------   Claudio HEINEN | {'age': '23', 'bday': '27/07/1992', 'nation': 'GERMANY'} Laurence BOURQUE |  {'age': '23', 'bday': '27/07/1992', 'nation': 'CANADA'}(2 rows)

  -

  SELECT *FROM cycling.birthday_listWHERE blist[ 'nation' ] = 'NETHERLANDS';

  cyclist_name  | blist---------------+-------------------------------------------------------------- Luc HAGENAARS | {'age': '28', 'bday': '27/07/1987', 'nation': 'NETHERLANDS'}   Toine POELS | {'age': '52', 'bday': '27/07/1963', 'nation': 'NETHERLANDS'}(2 rows)

  -

  CREATE TABLE IF NOT EXISTS cycling.birthday_list (  cyclist_name text PRIMARY KEY,  blist map<text, text>);

  CREATE INDEX IF NOT EXISTS blist_values_idxON cycling.birthday_list ( VALUES(blist) );

  SELECT *FROM cycling.birthday_listWHERE blist CONTAINS 'NETHERLANDS';

  +

  cyclist_name  | blist---------------+-------------------------------------------------------------- Luc HAGENAARS | {'age': '28', 'bday': '27/07/1987', 'nation': 'NETHERLANDS'}   Toine POELS | {'age': '52', 'bday': '27/07/1963', 'nation': 'NETHERLANDS'}(2 rows)

• FROZEN map. The table in this example stores the number of Pro wins, Grand Tour races, and Classic races that a cyclist has competed in. The SELECT statement finds any cyclist who has 39 Pro race wins, 7 Grand Tour starts, and 14 Classic starts.

  CREATE TABLE IF NOT EXISTS cycling.race_starts (  cyclist_name text PRIMARY KEY,  rnumbers FROZEN<LIST<int>>);

  CREATE INDEX IF NOT EXISTS rnumbers_idxON cycling.race_starts ( FULL(rnumbers) );

  SELECT *FROM cycling.race_startsWHERE rnumbers = [39, 7, 14];

  cyclist_name   | rnumbers----------------+------------- John DEGENKOLB | [39, 7, 14](1 rows)

  Check secondary index (2i) existence

  Verify that an index exists:
• CQL
• Result

  DESCRIBE TABLE cycling.birthday_list;

  TBD

  Alter a secondary index (2i)

  drop the index and create a new one.

  Drop a secondary index (2i)

  Drop a 2i:

  DROP INDEX IF EXISTS cycling.teams_idx;

  Querying using secondary indexes (2i)