CREATE INDEX (2i)

For performance and maintenance reasons, don’t create superfluous indexes, and don’t create multiple types of indexes on the same column. Identify the columns that are required for your query patterns, and then choose the one index type that best fits your use case.

DSE Search (Solr) indexes cannot be mixed with other index types anywhere on the same table.

If you need to index the same column multiple times, consider table-per-query and denormalization.

Secondary indexing (2i) is the original secondary indexing of CQL. 2i adds a single column-level index to one CQL table column. The indexes are locally built on each node in a cluster, so using 2i for queries results in poor performance.

Compared to newer types of indexing, 2i suffers from poor performance and high latency. If you are building new indexes, use SAI instead.

There are distinct conditions that determine when a 2i index is appropriate. If no other indexing option is suitable, 2i can be performant only when used for queries scoped to a single partition on a table with small partitions, and the index is built on a column that is rarely updated or deleted.

Don’t use 2i for the following use cases:

High-cardinality columns that are used to query huge datasets for a small number of results.
counter columns.
Columns with frequently updated or deleted values.
Broad queries on large partitions.
Queries using inequality or range operators, which require bypassing guardrails with ALLOW FILTERING.

For more information, see Indexes.

Create a 2i index

To create a 2i index, define the table name, column name, and the legacy_local_table index type.

If the column already contains data, the existing data is indexed during the execution of this statement. After an index has been created, it is automatically updated when data in the column changes.

This index type cannot be altered. If you need to modify an index, you must drop the index, and then create a new index.

CREATE CUSTOM INDEX [ IF NOT EXISTS ] [ <index_name> ]
  ON [ <keyspace_name>.]<table_name> (<column_name>)
    | [ (KEYS(<map_name>)) ]
    | [ (VALUES(<map_name>)) ]
    | [ (ENTRIES(<map_name>)) ]
  USING 'legacy_local_table'
  [ WITH OPTIONS = { <option_map> } ] ;

Legend
Syntax conventions	Description
UPPERCASE	Literal keyword.
Lowercase	Not literal.
`< >`	Variable value. Replace with a user-defined value.
`[]`	Optional. Square brackets (`[]`) surround optional command arguments. Do not type the square brackets.
`( )`	Group. Parentheses ( `( )` ) identify a group to choose from. Do not type the parentheses.
`\|`	Or. A vertical bar (`\|`) separates alternative elements. Type any one of the elements. Do not type the vertical bar.
`...`	Repeatable. An ellipsis ( `...` ) indicates that you can repeat the syntax element as often as required.
`'<Literal string>'`	Single quotation (`'`) marks must surround literal strings in CQL statements. Use single quotation marks to preserve upper case.
`{ <key> : <value> }`	Map collection. Braces (`{ }`) enclose map collections or key value pairs. A colon separates the key and the value.
`<<datatype1>,<datatype2>>`	Set, list, map, or tuple. Angle brackets ( `< >` ) enclose data types in a set, list, map, or tuple. Separate the data types with a comma.
`<cql_statement>;`	End CQL statement. A semicolon (`;`) terminates all CQL statements.
`[--]`	Separate the command line options from the command arguments with two hyphens ( `--` ). This syntax is useful when arguments might be mistaken for command line options.
`' <<schema\> ... </schema\>> '`	Search CQL only: Single quotation marks (`'`) surround an entire XML schema declaration.
`@<xml_entity>='<xml_entity_type>'`	Search CQL only: Identify the entity and literal value to overwrite the XML element in the schema and solrConfig files.

Options

IF NOT EXISTS: Allows silent skip by checking for existing indexes with the same name. If a match is found, the operation silently does nothing (no error is thrown). If no match is found, the operation creates the index.
index_name: Unique identifier for the index.

Enclose in quotes to preserve capitalization.

Must be unique across all indexes in the keyspace. Although indexes apply to a specific table and column, they are created at the keyspace level.

If no name is specified, the default is <table_name>_<column_name>_idx.
keyspace_name: Optional name of the keyspace that contains the table to index. If no name is specified, the current keyspace is used.
table_name: The name of the table on which the index is being defined.
column_name: The name of the column to index.

If used with a map, the column name is the map name. For maps, you can create indexes using the key, value, entry (a key:value pair), or full content of the collection.

counter columns cannot be indexed with 2i.

If a composite partition key is defined, a 2i index can be created on any of the partition key columns. A single-column partition key is already covered by the table’s primary key index. If you attempt to create an SAI index on a single-column partition key, you will receive an invalid query message.
map_name: Used with collections, identifier of the map_name specified in CREATE TABLE … map(<map_name>). The regular column syntax applies for collection types list and set.
WITH OPTIONS = { <option_map> }: The <option_map> is a map of options and their values defined in JSON simple format. Supported options are specific to the index type. Options aren’t required.

Query with a 2i index

2i secondary indexes are not a replacement for primary keys, but they can be used to improve query performance in some cases.

After you create a 2i secondary index, query your data with the 2i secondary index:

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)

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.

You must create a keyspace before you can create tables and indexes. To follow along with these examples, create a keyspace named cycling.

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.

In the cycling keyspace, create a table called alt_stats with one primary key column:

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

Index creation fails because the id column is the primary key and the only partition key. Such indexes are not allowed.

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

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

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

Example: SELECT 1 - error

This example shows that a query with only one of the partition key columns fails if ALLOW FILTERING is not used.

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;

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.

Example: SELECT 1A - race_year

This example shows that a query with one of the partition key columns will succeed without ALLOW FILTERING.

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
ALLOW FILTERING;

 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)

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.

Example: SELECT 2 - AND

This example shows that a query with both of the partition key columns using AND will succeed without ALLOW FILTERING.

You might 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';

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

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

Example: SELECT 1 - rank

This example shows a query with only the clustering column.

Query the table using only the rank column:

SELECT * FROM cycling.rank_by_year_and_name
  WHERE rank = 3;

 race_year | race_name                                  | rank | cyclist_name
-----------+--------------------------------------------+------+----------------------
      2014 |                        4th Tour of Beijing |    3 | Johan Esteban CHAVES
      2014 | Tour of Japan - Stage 4 - Minami > Shinshu |    3 |      Benjamin PRADES
      2015 | Tour of Japan - Stage 4 - Minami > Shinshu |    3 |         Thomas LEBAS

(3 rows)

Example: SELECT 2 - error

This example shows that a query with the clustering column and the partition key column fails without ALLOW FILTERING.

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;

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"

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.

Example: SELECT 2A - AND

This example shows a query with the clustering column and the partition key column succeeding with ALLOW FILTERING.

Fix the error for the query 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;

 race_year | race_name                                  | rank | cyclist_name
-----------+--------------------------------------------+------+----------------------
      2014 |                        4th Tour of Beijing |    3 | Johan Esteban CHAVES
      2014 | Tour of Japan - Stage 4 - Minami > Shinshu |    3 |      Benjamin PRADES

(2 rows)

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

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

 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 DATE column.
```
CREATE INDEX birthday_idx ON cycling.cyclist_alt_stats (birthday);
```

Query the table using the birthday column:

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

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

(1 rows)

Now that you have created indexes on the columns birthday and nationality, try these additional queries:

Example: SELECT 1 - AND

This example shows that a query with both the birthday and nationality columns will fail if ALLOW FILTERING is not used.

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.

Example: SELECT 2 - OR

This example shows that a query with either the birthday or nationality columns will not succeed because it is not supported.

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:

InvalidRequest: Error from server: code=2200 [Invalid query]
message="An index involved in this query does not support
disjunctive queries using the OR operator"

Example: SELECT 3 - IN

This example shows how to query with a list of nationality values.

Query the table using nationality with IN:

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

 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)

Example: SELECT 4 - RANGE

This example shows how to query with an inequality range of birthday values.

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

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

Example: SELECT 1 - equality

Query the table using the rank column in an equality:

SELECT * FROM cycling.rank_by_year_and_name
  WHERE rank = 3;

 race_year | race_name                                  | rank | cyclist_name
-----------+--------------------------------------------+------+----------------------
      2014 |                        4th Tour of Beijing |    3 | Johan Esteban CHAVES
      2014 | Tour of Japan - Stage 4 - Minami > Shinshu |    3 |      Benjamin PRADES
      2015 | Tour of Japan - Stage 4 - Minami > Shinshu |    3 |         Thomas LEBAS

(3 rows)

Example: SELECT 2 - inequality range

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

SELECT * FROM cycling.rank_by_year_and_name
  WHERE rank < 3;

When using a 2i index, this will fail without ALLOW FILTERING.

 race_year | race_name                                  | rank | cyclist_name
-----------+--------------------------------------------+------+----------------------
      2014 |                        4th Tour of Beijing |    1 |    Phillippe GILBERT
      2014 |                        4th Tour of Beijing |    2 |        Daniel MARTIN
      2014 | Tour of Japan - Stage 4 - Minami > Shinshu |    1 |        Daniel MARTIN
      2014 | Tour of Japan - Stage 4 - Minami > Shinshu |    2 | Johan Esteban CHAVES
      2015 |   Giro d'Italia - Stage 11 - Forli > Imola |    1 |        Ilnur ZAKARIN
      2015 |   Giro d'Italia - Stage 11 - Forli > Imola |    2 |      Carlos BETANCUR
      2015 | Tour of Japan - Stage 4 - Minami > Shinshu |    1 |      Benjamin PRADES
      2015 | Tour of Japan - Stage 4 - Minami > Shinshu |    2 |          Adam PHELAN

(8 rows)

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

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

Example: SELECT 1 - equality

This example shows that a query with the weight column can be performed using an equality.

Query the table using the weight column in an equality:

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

 lastname  | nationality | weight
-----------+-------------+--------
 VEIKKANEN |     Finland |     66

(1 rows)

Example: SELECT 2 - inequality range

This example shows that a query with the weight column can be performed using an inequality range.

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;

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

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

Example: SELECT 1 - equality

This example shows that a query with the created_at column can be performed using an equality.

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

 commenter | comment              | created_at
-----------+----------------------+---------------------------------
       Amy | Ride in any weather! | 2024-06-07 03:13:40.268000+0000

(1 rows)

SELECT 2 - inequality range

This example shows that a query with the created_at column can be performed using an inequality range.

For 2i, the ALLOW FILTERING directive is required for inequality queries or the query will fail.

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'
ALLOW FILTERING;

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

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

 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.

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

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

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

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

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

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

(1 rows)

Map - keys

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

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

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;

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

(1 rows)

Not all map columns are designed for useful searches on map keys. In some cases, indexing on map values or map entries produces more useful results. The following example demonstrates how to query with a map key index, but the map key index used in this example doesn’t produce useful results.

Create a table called cycling.birthday_list 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) );

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

 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; it has the same value for all rows.

Map - values

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

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

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

 firstname | lastname   | teams
-----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     Jamie |    BENNETT |                                                                     {2011: 'Team Garmin - Cervelo', 2013: 'Boels:Dolmans Cycling Team', 2014: 'Boels:Dolmans Cycling Team'}
 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'}

(2 rows)

Because the values of a map column are often unique, querying on a map value can be more useful than querying on map keys. This is demonstrated in the following example.

Create a table called cycling.birthday_list 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) );

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

 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

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

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_list 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_entries_idx
  ON cycling.birthday_list ( ENTRIES(blist) );

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

Example: SELECT 1

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

 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)

Example: SELECT 2

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

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

Example: DATE

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;

 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)

Example: AND

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;

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

(1 rows)

Queries on a map entry where the key is a unique identifier are structured differently, as shown in the following 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) );

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

Example: SELECT 1

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;

 firstname | lastname   | teams
-----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     Jamie |    BENNETT |                                                                     {2011: 'Team Garmin - Cervelo', 2013: 'Boels:Dolmans Cycling Team', 2014: 'Boels:Dolmans Cycling Team'}
 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'}

(2 rows)

Example: AND

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;

 firstname | lastname   | teams
-----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 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'}

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

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

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

(1 rows)

CREATE INDEX (2i)

Create a 2i index

Options

Query with a 2i index

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

See also

Was this helpful?

Give Feedback