Secondary index (2i) examples

2i indexing can be used to index and query both primary key columns and non-primary key columns of almost any type. Find the type of index you want to create and follow the examples to create and query the index.

Each example starts with a table creation, followed by index creation, so that you can see the table schema and the index schema. Then sample queries for using the index are presented in a tabbed codeblock, to make it easier to examine queries side-by-side.

A keyspace must be created before tables and indexes can be created.

The following keyspace is used for all examples on this page:

CREATE KEYSPACE IF NOT EXISTS cycling
WITH REPLICATION = {
  'class' : 'SimpleStrategy',
  'replication_factor' : 1
};

Primary key columns

Make an index on primary key columns, either composite partition key columns or clustering columns.

Single primary key column

This index creation will fail, because the table has a single PRIMARY KEY column. Such indexes are not allowed.

Create a table called cycling.alt_stats with a primary key if it doesn’t already exist.

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

Create a 2i index on the id column.

CREATE INDEX id_idx ON cycling.cyclist_alt_stats (id);

Results

InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot create 
secondary index on the only partition key column id"

This index creation will fail, because the id column is the primary key, as well as the single partition key. Such indexes are not allowed.

Composite partition key columns in primary key

A composite partition key uses two or more columns to define the partition key in the PRIMARY KEY definition. To filter a query using a column that is not the full partition key, you must create a 2i index on the column that you wish to use for the query.

Create a table called cycling.rank_by_year_and_name with a composite partition key and a clustering column if it doesn’t already exist.

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

Create two 2i indexes on the race_year and race_name columns. These two columns are the composite partition key in the PRIMARY KEY definition.

CREATE INDEX IF NOT EXISTS race_year_idx 
  ON cycling.rank_by_year_and_name (race_year);
CREATE INDEX IF NOT EXISTS race_name_idx 
  ON cycling.rank_by_year_and_name (race_name);

Verify the created indexes by checking the table schema with the DESCRIBE INDEX CQL command.
```
DESCRIBE INDEX cycling.race_year_idx;
DESCRIBE INDEX cycling.race_name_idx;
```
Results

Now that you have created the table and indexes, try some SELECT queries.

The first example tab shows that a query with only one of the partition key columns fails if ALLOW FILTERING is not used.
The second example tab shows that a query with one of the partition key columns will succeed without ALLOW FILTERING.
The third example tab shows that a query with both of the partition key columns using AND will succeed without ALLOW FILTERING.

SELECT 1 - error
SELECT 1A - race_year
SELECT 2 - AND

Query the table using only the race_year`column. A similar query using only the `race_name column can also be performed.

SELECT * FROM cycling.rank_by_year_and_name
  WHERE race_year = 2014 
  AND race_name  = 'Tour of Japan - Stage 4 - Minami > Shinshu';

Results

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"

For 2i indexes, you must use the ALLOW FILTERING directive when querying a table using a column that is not the full partition key.

To fix the error in SELECT 1, query the table using only the race_year`column, but add `ALLOW FILTERING.

SELECT * FROM cycling.rank_by_year_and_name
  WHERE race_year = 2014 
  AND race_name  = 'Tour of Japan - Stage 4 - Minami > Shinshu';

Results

 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)

Adding ALLOW FILTERING allows the query to succeed. However, this query is dangerous, because it requires a full table scan. SAI indexing is a better option that avoids this problem.

You may want to query the table using both the race_year and race_name columns. This query filters the results using both columns, so ALLOW FILTERING is not required.

SELECT * FROM cycling.rank_by_year_and_name
  WHERE race_year = 2014 
  AND race_name  = 'Tour of Japan - Stage 4 - Minami > Shinshu';

Results

 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)

Clustering column in primary key

A clustering column sorts the rows within a partition. Clustering columns are part of the PRIMARY KEY definition. To filter a query using just a clustering column, you must create a 2i index on the column that you wish to use for the query.

If you wish to use a clustering column in a query, it is best to consider restricting a query to a single partition. Using a partition key column plus a clustering column will have better performance.

Create a table called cycling.rank_by_year_and_name with a composite partition key and a clustering column if it doesn’t already exist.

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

Create a 2i index on the rank clustering column.

CREATE INDEX IF NOT EXISTS rank_idx 
  ON cycling.rank_by_year_and_name (rank);

Verify the created indexes by checking the schema with the DESCRIBE INDEX CQL command.
```
DESCRIBE INDEX cycling.rank_idx;
```
Results

Now that you have created the table and index, try some queries.

The first example tab shows a query with only the clustering column.
The second example tab shows that a query with the clustering column and the partition key column fails without ALLOW FILTERING.
The third example tab shows a query with the clustering column and the partition key column succeeding with ALLOW FILTERING.

SELECT 1 - rank
SELECT 2 - error
SELECT 2A - AND

Query the table using only the `rank`column.

SELECT * FROM cycling.rank_by_year_and_name WHERE rank = 3;

Results

Query the table using the race_year and `rank`columns.

SELECT * FROM cycling.rank_by_year_and_name
  WHERE race_year = 2014 AND rank = 3;

Results

Adding ALLOW FILTERING allows the query to succeed. However, this query is dangerous, because it requires a full table scan. SAI indexing is a better option that avoids this problem.

Fix the query the table using the race_year and rank columns by adding ALLOW FILTERING.

SELECT * FROM cycling.rank_by_year_and_name
  WHERE race_year = 2014 AND rank = 3
ALLOW FILTERING;

Results

Non-primary key columns

Indexes can be created on non-primary key columns of almost any type.

Text column

Create a table called cycling.alt_stats with TEXT column called nationality if it doesn’t already exist.

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

Create a 2i index on the nationality column, a non-primary key TEXT column.
```
CREATE INDEX nationality_idx ON cycling.cyclist_alt_stats (nationality);
```

Verify the created indexes by checking the index schema:

CREATE CUSTOM INDEX nationality_idx ON cycling.cyclist_alt_stats (nationality) USING 'sai';

Results

CREATE CUSTOM INDEX nationality_idx ON cycling.cyclist_alt_stats (nationality) USING 'sai';

Now that you have created the table and index, query using a value for the column.

Query the table using the nationality column.

SELECT first_race, last_race, birthday FROM cycling.cyclist_alt_stats
  WHERE nationality = 'France';

Results

 first_race | last_race  | birthday
------------+------------+------------
 2006-03-15 | 2017-04-16 | 1990-05-27

(1 rows)

Date column

Create a table called cycling.alt_stats with DATE column called birthday if it doesn’t already exist.

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

Create a 2i index on the birthday column, a non-primary key TEXT column.
```
CREATE INDEX birthday_idx ON cycling.cyclist_alt_stats (birthday);
```

Verify the created indexes by checking the index schema:

CREATE CUSTOM INDEX birthday_idx ON cycling.cyclist_alt_stats (birthday) USING 'sai';

Results

CREATE CUSTOM INDEX birthday_idx ON cycling.cyclist_alt_stats (birthday) USING 'sai';

Now that you have created the table and index, query using a value for the column.

Query the table using the birthday column.

SELECT lastname, birthday, nationality FROM cycling.cyclist_alt_stats
  WHERE birthday = '1981-03-29';

+ .Results

Details

 lastname  | birthday   | nationality
-----------+------------+-------------
 VEIKKANEN | 1981-03-29 |     Finland

(1 rows)

Now that you have created indexes on the columns birthday and nationality, there are a number of other queries that you can perform.

The first example tab, SELECT 1 - AND, shows that a query with both the birthday and nationality columns will fail if ALLOW FILTERING is not used.
The second example tab, SELECT 2 - OR, shows that a query with either the birthday or nationality columns will not succeed because it is not supported.
The third example tab, SELECT 3 - IN, shows how to query with a list of nationality values.
The fourth example tab, SELECT 4 - RANGE, shows how to query with an inequality range of birthday values.

SELECT 1 - AND
SELECT 2 - OR
SELECT 3 - IN
SELECT 4 - RANGE

Query the table using both a nationality and a birthday:

SELECT lastname, birthday, nationality FROM cycling.cyclist_alt_stats
  WHERE birthday = '1991-08-25' AND nationality = 'Ethiopia'
ALLOW FILTERING;

The query fails:

 lastname | birthday   | nationality
----------+------------+-------------
    GRMAY | 1991-08-25 |    Ethiopia

(1 rows)

The indexes have been created on appropriate low-cardinality columns, but querying with ALLOW FILTERING still fails.

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.

Adding ALLOW FILTERING allows the query to succeed. However, this query is dangerous, because it requires a full table scan. SAI indexing is a better option that avoids this problem.

Query the table using a nationality or a birthday:

  SELECT lastname, birthday, nationality FROM cycling.cyclist_alt_stats
    WHERE birthday = '1991-08-25' OR nationality = 'Ethiopia'
  ALLOW FILTERING;

OR is not an allowed operator for 2i indexes.

Query the table using nationality with IN:

SELECT * FROM cycling.cyclist_alt_stats 
  WHERE nationality IN ('Finland', 'France')
ALLOW FILTERING;

Results

 id                                   | birthday   | first_race | height | last_race  | lastname  | nationality | w_units | weight
--------------------------------------+------------+------------+--------+------------+-----------+-------------+---------+--------
 c09e9451-50da-483d-8108-e6bea2e827b3 | 1981-03-29 | 1996-05-21 |   1.78 | 2012-10-02 | VEIKKANEN |     Finland |      kg |     66
 4ceb495c-55ab-4f71-83b9-81117252bb13 | 1990-05-27 | 2006-03-15 |   null | 2017-04-16 |     DUVAL |      France |    null |   null

(2 rows)

Query the table using a birthday range:

SELECT lastname, birthday, nationality FROM cycling.cyclist_alt_stats
  WHERE birthday >'1981-03-29'
ALLOW FILTERING;

Inequalities are allowed for 2i indexes, but only with ALLOW FILTERING. Again, the difficulties of using 2is is illustrated. SAI is almost always a better option.

 lastname  | birthday   | nationality
-----------+------------+-------------
     BRUTT | 1982-01-29 |      Russia
  ISAYCHEV | 1986-04-21 |      Russia
 TSATEVICH | 1989-07-05 |      Russia
  PAULINHO | 1990-05-27 |    Portugal
     GRMAY | 1991-08-25 |    Ethiopia
    BELKOV | 1985-01-09 |      Russia
     DUVAL | 1990-05-27 |      France

(7 rows)

Integer column

An integer column can be indexed similarly to a text column.

Create a table called cycling.rank_by_year_and_name with INT column called rank if it doesn’t already exist.

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

Create a 2i index on the rank column, a non-primary key INT column.

CREATE INDEX IF NOT EXISTS rank_idx 
  ON cycling.rank_by_year_and_name (rank);

Verify the created indexes by checking the index schema.
```
DESCRIBE INDEX cycling.rank_idx;
```
Results

Now that you have created the table and index, query using a value for the column.

SELECT 1 - equality
SELECT 2 - inequality range

Query the table using the rank column in an equality:

SELECT * FROM cycling.rank_by_year_and_name WHERE rank = 3;

Results

Query the table using the rank column in an inequality range.

SELECT * FROM cycling.rank_by_year_and_name WHERE rank < 3;

Note that this will fail without ALLOW FILTERING with a 2i index.

Results

Float column

Float columns can be indexed and searched.

Create a table called cycling.cyclist_alt_stats with FLOAT column called weight if it doesn’t already exist.

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

Create a 2i index on the weight column, a non-primary key FLOAT column.
```
CREATE INDEX weight_idx ON cycling.cyclist_alt_stats (weight);
```

Verify the created indexes by checking the table schema.

Results

CREATE CUSTOM INDEX weight_idx ON cycling.cyclist_alt_stats (weight) USING 'sai';

Now that you have created the table and index, query using a value for the column.

The first example tab shows that a query with the weight column can be performed using an equality.
The second example tab shows that a query with the weight column can be performed using an inequality range.

SELECT 1 - equality
SELECT 2 - inequality range

Query the table using the weight column in an equality:

SELECT lastname, nationality, weight FROM cycling.cyclist_alt_stats 
 WHERE weight = 70 ALLOW FILTERING;

Results

 lastname | nationality | weight
----------+-------------+--------


(0 rows)

Query the table using the weight column in an inequality range:

SELECT lastname, nationality, weight FROM cycling.cyclist_alt_stats 
 WHERE weight > 70 ALLOW FILTERING;

Results

 lastname | nationality | weight
----------+-------------+--------
 ISAYCHEV |      Russia |     80
   BELKOV |      Russia |     71

(2 rows)

Timestamp column

Timestamp columns can be indexed and searched.

Create a table called cycling.comments with TIMESTAMP column called created_at if it doesn’t already exist.

CREATE TABLE IF NOT EXISTS cycling.comments (
  record_id timeuuid,
  id uuid,
  commenter text,
  comment text,
  created_at timestamp,
  PRIMARY KEY (id, created_at)
)
WITH CLUSTERING ORDER BY (created_at DESC);

Create a 2i index on the comments column, a non-primary key TIMESTAMP column.

CREATE INDEX IF NOT EXISTS created_at_idx ON cycling.comments (created_at);

Verify the created indexes by checking the index schema.

DESCRIBE INDEX cycling.created_at_idx;

Results

CREATE CUSTOM INDEX created_at_idx ON cycling.comments (created_at) USING 'sai';

Now that you have created the table and index, query using a value for the column.

The first example tab shows that a query with the created_at column can be performed using an equality.
The second example tab shows that a query with the created_at column can be performed using an inequality range.

SELECT 1 - equality
SELECT 2 - inequality range

Query the table using the created_at column in an equality:

SELECT commenter, comment, created_at FROM cycling.comments
  WHERE created_at = '2024-06-07 03:13:40.268';

Results

 commenter | comment | created_at
-----------+---------+------------


(0 rows)

Query the table using the created_at column in an inequality range:

SELECT commenter, comment, created_at FROM cycling.comments
  WHERE created_at < '2024-06-07 03:13:40.268'

Results

 commenter | comment                                | created_at
-----------+----------------------------------------+---------------------------------
      Alex | Raining too hard should have postponed | 2023-04-01 11:21:59.001000+0000

(1 rows)

For 2i, note that the ALLOW FILTERING directive is required for inequality queries or the query will fail. Try it out yourself!

Tuple column

Tuples columns can be indexed and searched, but only the full tuple can be indexed.

Create a table called cycling.nation_rank with TUPLE column called info if it doesn’t already exist.

CREATE TABLE IF NOT EXISTS cycling.nation_rank (
  nation text PRIMARY KEY,
  info tuple<int, text, int>
);

Create a 2i index on the info column, a non-primary key TUPLE column.
```
CREATE INDEX info_idx ON cycling.nation_rank (info);
```

Verify the created indexes by checking the table schema.

DESCRIBE INDEX cycling.info_idx;

Results

CREATE TABLE cycling.nation_rank (
    nation text PRIMARY KEY,
    info frozen<tuple<int, text, int>>
) WITH additional_write_policy = '99p'
    AND allow_auto_snapshot = true
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND cdc = false
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.UnifiedCompactionStrategy', 'max_sstables_to_compact': '64', 'min_sstable_size': '100MiB', 'scaling_parameters': 'T4', 'sstable_growth': '0.3333333333333333', 'target_sstable_size': '1GiB'}
    AND compression = {'chunk_length_in_kb': '16', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND memtable = 'default'
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND extensions = {}
    AND gc_grace_seconds = 864000
    AND incremental_backups = true
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99p';


CREATE CUSTOM INDEX info_idx ON cycling.nation_rank (info) USING 'sai';

Now that you have created the table and index, query using a value for the column.

Query the table using the info column in an equality. Tuples can only be filtered using the full tuple value.

SELECT * FROM cycling.nation_rank
  WHERE info = (3, 'Phillippe GILBERT', 6222);

Results

 nation  | info
---------+--------------------------------
 Belgium | (3, 'Phillippe GILBERT', 6222)

(1 rows)

Non-primary key 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.

Sets and lists can index all values found by indexing the collection column. Maps can index a map key, map value, or map entry using the methods shown below. Multiple indexes can be created on the same map column in a table so that map keys, values, or entries can be queried. In addition, frozen collections can be indexed using FULL to index the full content of a frozen collection.

All the potential problems with 2i apply to 2i indexes on collections.

Set

Create an index on a set to find all the cyclists that have been on a particular team.

Create a table called cycling.cyclist-career-teams if it doesn’t already exist.

CREATE TABLE IF NOT EXISTS cycling.cyclist_career_teams (
  id UUID PRIMARY KEY,
  lastname text,
  teams set<text>
);

Create a 2i index on the teams column.

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

Verify the created indexes by checking the index schema.

DESCRIBE INDEX cycling.teams_idx;

Results

CREATE CUSTOM INDEX teams_idx ON cycling.cyclist_career_teams (values(teams)) USING 'sai';

Now that you have created the table and index, query using a value for the column.

Query the table using the teams column with a CONTAINS phrase to get the rows where a set contains a particular value.

SELECT lastname, teams FROM cycling.cyclist_career_teams 
  WHERE teams CONTAINS 'Rabobank-Liv Giant';

Results

 lastname | teams
----------+------------------------------------------------------------------------------------------------------
      VOS | {'Nederland bloeit', 'Rabobank Women Team', 'Rabobank-Liv Giant', 'Rabobank-Liv Woman Cycling Team'}
    BRAND |   {'AA Drink - Leontien.nl', 'Leontien.nl', 'Rabobank-Liv Giant', 'Rabobank-Liv Woman Cycling Team'}

(2 rows)

List

Create an index on a set to find all the cyclists that have a particular sponsor.

Create a table called cycling.race_sponsors if it doesn’t already exist.

CREATE TABLE IF NOT EXISTS cycling.race_sponsors (
  race_year int,
  race_name text,
  sponsorship list<text>,
  PRIMARY KEY (race_year, race_name)
);

Create a 2i index on the sponsorship list column.

CREATE INDEX sponsorship_idx ON cycling.race_sponsors (sponsorship);

Verify the created index by checking the index schema.

DESCRIBE INDEX cycling.sponsorship_idx;

Results

CREATE CUSTOM INDEX sponsorship_idx ON cycling.race_sponsors (values(sponsorship)) USING 'sai';

Now that you have created the table and index, query using a value for the column.

Query the table using the sponsorship column with a CONTAINS phrase to get the rows where a set contains a particular value.

SELECT * FROM cycling.race_sponsors WHERE sponsorship CONTAINS 'Carrefour';

Results

 race_year | race_name      | sponsorship
-----------+----------------+-------------------------------------------------
      2018 | Tour de France | ['LCL', 'Carrefour', 'Skoda', 'Vittel', 'Krys']

(1 rows)

For map collections, create an index on the map key, map value, or map entry.

Map - keys

Create an index on a map key to find all cyclist/team combinations for a particular year.

Create a table called cycling.cyclist_teams if it doesn’t already exist.

CREATE TABLE IF NOT EXISTS cycling.cyclist_teams (
  id uuid PRIMARY KEY,
  firstname text,
  lastname text,
  teams map<int, text>
);

Create a 2i index on the teams map column.

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

Verify the created 2i index by checking the index schema.

DESCRIBE index cycling.team_keys_idx;

Results

CREATE CUSTOM INDEX team_keys_idx ON cycling.cyclist_teams (keys(teams)) USING 'sai';

Now that you have created the table and index, query using a key for the map column.

Query the table using the teams column with a CONTAINS KEY phrase to get the rows where a map contains a particular key.

SELECT firstname,lastname,teams FROM cycling.cyclist_teams 
  WHERE teams CONTAINS KEY 2015;

Results

 firstname | lastname | teams
-----------+----------+------------------------------------------------------------------------------------
  Marianne |      VOS | {2014: 'Rabobank-Liv Woman Cycling Team', 2015: 'Rabobank-Liv Woman Cycling Team'}

(1 rows)

Note that not all map columns are designed to make search via the map key useful. For example, look at this next query.

Create a table called cycling.birthday if it doesn’t already exist.

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

Create a SAI index on the blist map column.

CREATE INDEX IF NOT EXISTS blist_keys_idx 
  ON cycling.birthday_list ( KEYS(blist) );

Verify the created index by checking the index schema.

DESCRIBE INDEX cycling.blist_keys_idx;

+ .Results

Details

CREATE CUSTOM INDEX blist_keys_idx ON cycling.birthday_list (keys(blist)) USING 'sai';

Now that you have created the table and index, query using a key for the map column.

Query the table using the blist column with a CONTAINS KEY phrase to get the rows where a map contains a particular key.

SELECT * FROM cycling.birthday_list WHERE blist CONTAINS KEY 'age';

Results

 cyclist_name     | blist
------------------+--------------------------------------------------------------
   Claudio HEINEN |     {'age': '23', 'bday': '27/07/1992', 'nation': 'GERMANY'}
 Claudio VANDELLI |       {'age': '54', 'bday': '27/07/1961', 'nation': 'ITALY'}
    Luc HAGENAARS | {'age': '28', 'bday': '27/07/1987', 'nation': 'NETHERLANDS'}
      Toine POELS | {'age': '52', 'bday': '27/07/1963', 'nation': 'NETHERLANDS'}
      Allan DAVIS |   {'age': '35', 'bday': '27/07/1980', 'nation': 'AUSTRALIA'}
 Laurence BOURQUE |      {'age': '23', 'bday': '27/07/1992', 'nation': 'CANADA'}

(6 rows)

In this case, the map key is not a useful index because the map key is not a unique identifier, but the same value for all rows.

Map - values

Create an index on the map values and find cyclists who have a particular value found in the specified map.

Create a table called cycling.cyclist_teams if it doesn’t already exist.

CREATE TABLE IF NOT EXISTS cycling.cyclist_teams (
  id uuid PRIMARY KEY,
  firstname text,
  lastname text,
  teams map<int, text>
);

Create a 2i index on the teams map column.

CREATE INDEX IF NOT EXISTS team_values_idx
  ON cycling.cyclist_teams ( VALUES (teams) );

Verify the created index by checking the index schema.

DESCRIBE index cycling.team_values_idx;

Results

CREATE CUSTOM INDEX team_values_idx ON cycling.cyclist_teams (values(teams)) USING 'sai';

Now that you have created the table and index, query using a value for the map column:
```
SELECT firstname,lastname,teams FROM cycling.cyclist_teams
WHERE teams CONTAINS 'Team Garmin - Cervelo';
```
Results
firstname | lastname | teams -----------+----------+------- (0 rows)
Because the values of a map column are commonly unique, querying on a map value is more generally useful.

Create a table called cycling.birthday if it doesn’t already exist:

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

Create a 2i index on the blist map column.

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

Verify the created index by checking the table schema.

DESCRIBE INDEX cycling.blist_values_idx;

Results

CREATE CUSTOM INDEX blist_values_idx ON cycling.birthday_list (values(blist)) USING 'sai';

Now that you have created the table and index, query using a value for the map column:

SELECT * FROM cycling.birthday_list WHERE blist CONTAINS 'NETHERLANDS';

Results

 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)

Map - entries

Create an index on the map entries and find cyclists who are the same age. An index using ENTRIES is only valid for maps.

Create a table called cycling.birthday if it doesn’t already exist.

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

Create a 2i index on the blist map column.

CREATE INDEX IF NOT EXISTS team_entries_idx
  ON cycling.cyclist_teams ( ENTRIES (teams) );

Verify the created index by checking the index schema.

DESCRIBE index cycling.team_entries_idx;

Results

CREATE CUSTOM INDEX team_entries_idx ON cycling.cyclist_teams (entries(teams)) USING 'sai';

Now that you have created the table and index, query using an entry for the map column.

SELECT 1
SELECT 2
DATE
AND

Query the table using the age column with a CONTAINS ENTRY phrase to get the rows where a map contains a particular entry:

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

Results

 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)

Using the same index, find cyclists from the same country:

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

Results

 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)

Any number of different queries can be made using the ENTRIES index, such as selecting a matching entry with a DATE data type:

SELECT * FROM cycling.birthday_list WHERE blist [ 'bday' ] = '27/07/1992'
ALLOW FILTERING;

Results

 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)

Two different matches can be made using AND and the ENTRIES index:

SELECT * FROM cycling.birthday_list WHERE blist[ 'nation' ] = 'CANADA' 
  AND blist[ 'age' ] = '23' ALLOW FILTERING;

Results

 cyclist_name     | blist
------------------+---------------------------------------------------------
 Laurence BOURQUE | {'age': '23', 'bday': '27/07/1992', 'nation': 'CANADA'}

(1 rows)

The queries using a map entry where the key is a unique identifier do look slightly different. Study the example below and compare it to the previous example.

Create a table called cycling.cyclist-teams if it doesn’t already exist.

CREATE TABLE IF NOT EXISTS cycling.cyclist_teams (
  id uuid PRIMARY KEY,
  firstname text,
  lastname text,
  teams map<int, text>
);

Create a 2i index on the teams map column.

CREATE INDEX IF NOT EXISTS team_entries_idx
  ON cycling.cyclist_teams ( ENTRIES (teams) );

Verify the created index by checking the index schema.

DESCRIBE index cycling.team_entries_idx;

Results

CREATE CUSTOM INDEX team_entries_idx ON cycling.cyclist_teams (entries(teams)) USING 'sai';

Now query the table.

SELECT 1
AND

Query the table using both a key and a value to get the rows where a map contains a particular entry:

SELECT firstname,lastname,teams FROM cycling.cyclist_teams
  WHERE teams[2014] = 'Boels:Dolmans Cycling Team' ALLOW FILTERING;

Results

 firstname | lastname | teams
-----------+----------+-------


(0 rows)

Two different matches can be made using AND and the ENTRIES index:

SELECT firstname,lastname,teams FROM cycling.cyclist_teams
  WHERE teams[2014] = 'Boels:Dolmans Cycling Team'
    AND teams[2015] = 'Boels:Dolmans Cycling Team'
ALLOW FILTERING;

Results

 firstname | lastname | teams
-----------+----------+-------


(0 rows)

Frozen collection

Create an index on the full content of a FROZEN list. 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.

The FROZEN keyword is required to index the full content of a frozen collection and applies to any collection type: set, list, or map.

Create a table called race_starts if it does not yet exist.

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

Create a 2i index on the rnumbers frozen list column.

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

Verify the created index by checking the index schema.

DESCRIBE INDEX cycling.rnumbers_idx;

Results

CREATE CUSTOM INDEX rnumbers_idx ON cycling.race_starts (full(rnumbers)) USING 'sai';

Now that you have created the table and index, query using a value for the column.

Query the table using the rnumbers column with a CONTAINS phrase to get the rows where a frozen list contains a particular value:

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

Results

 cyclist_name   | rnumbers
----------------+-------------
 John DEGENKOLB | [39, 7, 14]

(1 rows)

Secondary index (2i) examples

Primary key columns

Single primary key column

Composite partition key columns in primary key

Clustering column in primary key

Non-primary key columns

Text column

Date column

Integer column

Float column

Timestamp column

Tuple column

Non-primary key collection column

Set

List

Map - keys

Map - values

Map - entries

Frozen collection

Was this helpful?

Give Feedback