CREATE INDEX

Defines a new index for a single column of a table.

Apache Cassandra supports creating an index on most columns, including the partition and cluster columns of a PRIMARY KEY, collections, and static columns. For maps, you can index using the key, value, or entries (a key:value pair).

See also: CREATE CUSTOM INDEX for Storage-Attached Indexes (SAI), DROP INDEX

Syntax

BNF definition:

  1. index_name::= re('[a-zA-Z_0-9]+')
  1. CREATE INDEX [ IF NOT EXISTS ] <index_name>
  2.   ON [<keyspace_name>.]<table_name>
  3.   ([ ( KEYS | FULL ) ] <column_name>)
  4.   (ENTRIES <column_name>)
  5.     //   | [ (KEYS(<map_name>)) ]
  6.     // | [ (VALUES(<map_name>)) ]
  7.     // | [ (ENTRIES(<map_name>)) ]
  8.    [USING 'sai'
  9.   [ WITH OPTIONS = { <option_map> } ]];

Syntax legend

Legend
Syntax conventionsDescription

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.

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

Required parameters

table_name

Name of the table to index.

column_name

Name of the column to index.

Optional parameters

index_name

Name of the index. Enclose in quotes to use special characters or preserve capitalization. If no name is specified, Apache Cassandra names the index: <table_name>_<column_name>_idx.

keyspace_name

Name of the keyspace that contains the table to index. If no name is specified, the current keyspace is used.

Usage notes

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

Indexing with the CREATE INDEX command can impact performance. Before creating an index, be aware of when and when not to create an index.

Restriction: Indexing counter columns is not supported.

Examples

Creating an index on a clustering column

Define a table having a composite partition key, and then create an index on a clustering column.

  • CREATE TABLE

  • CREATE INDEX

  • SELECT query

  • SELECT result

  1. CREATE TABLE IF NOT EXISTS cycling.rank_by_year_and_name (
  2.   race_year int,
  3.   race_name text,
  4.   cyclist_name text,
  5.   rank int,
  6.   PRIMARY KEY ((race_year, race_name), rank)
  7. );
  1. CREATE INDEX IF NOT EXISTS rank_idx
  2. ON cycling.rank_by_year_and_name (rank);
  1. SELECT * FROM rank_by_year_and_name WHERE rank = 1;
  1.  race_year | race_name                                  | rank | cyclist_name
  2. -----------+--------------------------------------------+------+-------------------
  3.       2014 |                        4th Tour of Beijing |    1 | Phillippe GILBERT
  4.       2014 | Tour of Japan - Stage 4 - Minami > Shinshu |    1 |     Daniel MARTIN
  5.       2015 |   Giro d'Italia - Stage 11 - Forli > Imola |    1 |     Ilnur ZAKARIN
  6.       2015 | Tour of Japan - Stage 4 - Minami > Shinshu |    1 |   Benjamin PRADES
  8. (4 rows)

Creating an index on a set or list collection

Create an index on a set or list collection column as you would any other column. Enclose the name of the collection column in parentheses at the end of the CREATE INDEX statement. For example, add a collection of teams to the cyclist_career_teams table to index the data in the teams set.

  • CREATE TABLE

  • CREATE INDEX

  • SELECT query

  • SELECT result

  1. CREATE TABLE IF NOT EXISTS cycling.cyclist_career_teams (
  2.   id UUID PRIMARY KEY,
  3.   lastname text,
  4.   teams set<text>
  5. );
  1. CREATE INDEX IF NOT EXISTS teams_idx
  2. ON cycling.cyclist_career_teams (teams);
  1. SELECT * FROM cyclist_career_teams WHERE teams CONTAINS 'Rabobank-Liv Woman Cycling Team';
  1.  id                                   | lastname        | teams
  2. --------------------------------------+-----------------+------------------------------------------------------------------------------------------------------
  3.  5b6962dd-3f90-4c93-8f61-eabfa4a803e2 |             VOS | {'Nederland bloeit', 'Rabobank Women Team', 'Rabobank-Liv Giant', 'Rabobank-Liv Woman Cycling Team'}
  4.  1c9ebc13-1eab-4ad5-be87-dce433216d40 |           BRAND |   {'AA Drink - Leontien.nl', 'Leontien.nl', 'Rabobank-Liv Giant', 'Rabobank-Liv Woman Cycling Team'}
  5.  e7cd5752-bc0d-4157-a80f-7523add8dbcd | VAN DER BREGGEN |                 {'Rabobank-Liv Woman Cycling Team', 'Sengers Ladies Cycling Team', 'Team Flexpoint'}
  7. (3 rows)

Creating an index on map keys

You can create an index on map collection keys. If an index of the map values of the collection exists, drop that index before creating an index on the map collection keys. Assume a cyclist table contains this map data where nation is the map key and `Canada is the map value`:

  1. {'nation':'CANADA' }

To index map keys, use the KEYS keyword and map name in nested parentheses in the CREATE INDEX statement. To run a SELECT query on the table, use CONTAINS KEY in WHERE clauses. This query returns cyclist teams that have an entry for the year 2015.

  • CREATE TABLE

  • CREATE INDEX

  • SELECT query

  • SELECT result

  1. CREATE TABLE IF NOT EXISTS cycling.cyclist_teams (
  2.   id uuid PRIMARY KEY,
  3.   firstname text,
  4.   lastname text,
  5.   teams map<int, text>
  6. );
  1. CREATE INDEX IF NOT EXISTS team_year_keys_idx
  2. ON cycling.cyclist_teams ( KEYS (teams) );
  1. SELECT *
  2. FROM cycling.cyclist_teams
  3. WHERE teams CONTAINS KEY 2015;
  1.  id                                   | firstname | lastname   | teams
  2. --------------------------------------+-----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  3.  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'}
  4.  5b6962dd-3f90-4c93-8f61-eabfa4a803e2 |  Marianne |        VOS |                                                                                                                                   {2015: 'Rabobank-Liv Woman Cycling Team'}
  6. (2 rows)

Creating an index on map entries

You can create an index on map entries. An ENTRIES index can be created only on a map column of a table that doesn’t have an existing index.

To index collection entries, use the ENTRIES keyword and map name in nested parentheses. To query the map entries in the table, use a WHERE clause with the map name and a value. This query finds cyclists who are the same age.

  • CREATE TABLE

  • CREATE INDEX

  • SELECT query

  • SELECT result

  1. CREATE TABLE IF NOT EXISTS cycling.birthday_list (
  2.   cyclist_name text PRIMARY KEY,
  3.   blist map<text, text>
  4. );
  1. CREATE INDEX IF NOT EXISTS blist_idx
  2. ON cycling.birthday_list ( ENTRIES(blist) );
  1. SELECT *
  2. FROM cycling.birthday_list
  3. WHERE blist[ 'age' ] = '23';
  1.  cyclist_name     | blist
  2. ------------------+----------------------------------------------------------
  3.    Claudio HEINEN | {'age': '23', 'bday': '27/07/1992', 'nation': 'GERMANY'}
  4.  Laurence BOURQUE |  {'age': '23', 'bday': '27/07/1992', 'nation': 'CANADA'}
  6. (2 rows)

Use the same index to find cyclists from the same country:

  • CREATE TABLE

  • CREATE INDEX

  • SELECT query

  • SELECT result

  1. CREATE TABLE IF NOT EXISTS cycling.birthday_list (
  2.   cyclist_name text PRIMARY KEY,
  3.   blist map<text, text>
  4. );
  1. CREATE INDEX IF NOT EXISTS blist_idx
  2. ON cycling.birthday_list ( ENTRIES(blist) );
  1. SELECT *
  2. FROM cycling.birthday_list
  3. WHERE blist[ 'nation' ] = 'NETHERLANDS';
  1.  cyclist_name  | blist
  2. ---------------+--------------------------------------------------------------
  3.  Luc HAGENAARS | {'age': '28', 'bday': '27/07/1987', 'nation': 'NETHERLANDS'}
  4.    Toine POELS | {'age': '52', 'bday': '27/07/1963', 'nation': 'NETHERLANDS'}
  6. (2 rows)

Creating an index on map values

To create an index on map values, use the VALUES keyword and map name in nested parentheses. To query the table, use a WHERE clause with the map name and the value it contains.

  • CREATE TABLE

  • CREATE INDEX

  • SELECT query

  • SELECT result

  1. CREATE TABLE IF NOT EXISTS cycling.birthday_list (
  2.   cyclist_name text PRIMARY KEY,
  3.   blist map<text, text>
  4. );
  1. CREATE INDEX IF NOT EXISTS blist_values_idx
  2. ON cycling.birthday_list ( VALUES(blist) );
  1. SELECT *
  2. FROM cycling.birthday_list
  3. WHERE blist CONTAINS 'NETHERLANDS';
  1.  cyclist_name  | blist
  2. ---------------+--------------------------------------------------------------
  3.  Luc HAGENAARS | {'age': '28', 'bday': '27/07/1987', 'nation': 'NETHERLANDS'}
  4.    Toine POELS | {'age': '52', 'bday': '27/07/1963', 'nation': 'NETHERLANDS'}
  6. (2 rows)

Creating an index on the full content of a frozen collection

You can create an index on a full FROZEN collection. A FULL index can be created on a set, list, or map column of a table that doesn’t have an existing index.

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. To index collection entries, use the FULL keyword and collection name in nested parentheses. For example, index the frozen list rnumbers. To query the table, use a WHERE clause with the collection name and values.

  • CREATE TABLE

  • CREATE INDEX

  • SELECT query

  • SELECT result

  1. CREATE TABLE IF NOT EXISTS cycling.race_starts (
  2.   cyclist_name text PRIMARY KEY,
  3.   rnumbers FROZEN<LIST<int>>
  4. );
  1. CREATE INDEX IF NOT EXISTS rnumbers_idx
  2. ON cycling.race_starts ( FULL(rnumbers) );
  1. SELECT *
  2. FROM cycling.race_starts
  3. WHERE rnumbers = [39, 7, 14];
  1.  cyclist_name   | rnumbers
  2. ----------------+-------------
  3.  John DEGENKOLB | [39, 7, 14]
  5. (1 rows)