CREATE INDEX

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

Define a new index on a single column of a table. 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. DataStax Enterprise supports creating an index on most columns, including the partition and cluster columns of a PRIMARY KEY, collections, and static columns. Indexing 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. For maps, index the key, value, or entries.

Synopsis

CREATE INDEX [ IF NOT EXISTS ] index_name
  ON [keyspace_name.]table_name
  ([ ( KEYS | FULL ) ] column_name) 
  (ENTRIES column_name);

Table 1. Legend
Syntax conventions	Description
UPPERCASE	Literal keyword.
Lowercase	Not literal.
`Italics`	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.

index_name: Optional identifier for index. If no name is specified, DataStax Enterprise names the index: table_name_column_name_idx. Enclose in quotes to use special characters or preserve capitalization.

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 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 INDEX rank_idx ON
cycling.rank_by_year_and_name (rank);

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 cycling.cyclist_career_teams (
  id UUID PRIMARY KEY,
  lastname text,
  teams set<text>
);

CREATE INDEX teams_idx
ON cycling.cyclist_career_teams (teams) ;

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:

{'nation':'CANADA' }

The map key is located to the left of the colon, and the map value is located to the right of the colon.

To index map keys, use the KEYS keyword and map name in nested parentheses:

CREATE INDEX team_year_idx
ON cycling.cyclist_teams ( KEYS (teams) );

To query the table, you can use CONTAINS KEY in WHERE clauses.

SELECT *
FROM cycling.cyclist_teams
WHERE teams CONTAINS KEY 2015;

The example returns cyclist teams that have an entry for the year 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 |                                                                                          {2014: 'Rabobank-Liv Woman Cycling Team', 2015: 'Rabobank-Liv Woman Cycling Team'}

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:

CREATE INDEX blist_idx 
   ON cycling.birthday_list (ENTRIES(blist));

To query the map entries in the table, use a WHERE clause with the map name and a value.

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

The example finds cyclists who are the same age.

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

Use the same index to 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'}

Creating an index on map values

To create an index on map values, use the VALUES keyword and map name in nested parentheses:

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

To query the table, use a WHERE clause with the map name and the value it contains.

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

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.

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

To index collection entries, use the FULL keyword and collection name in nested parentheses. For example, index the frozen list rnumbers.

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

To query the table, use a WHERE clause with the collection name and values:

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

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