CREATE INDEX

Define a new index on a single column of a table.

Synopsis

CREATE CUSTOM INDEX IF NOT EXISTS index_name
ON keyspace_name.table_name ( KEYS ( column_name ) )
USING class_name WITH OPTIONS = map

Restrictions: Using class_name is allowed only if CUSTOM is used and class_name is a string literal containing a java class name.

index_name is an identifier, enclosed or not enclosed in double quotation marks, excluding reserved words.

map is a map collection, a JSON-style array of literals:

{ literal : literal, literal : literal ... }

Description

CREATE INDEX creates a new index on the given table for the named column. Attempting to create an already existing index will return an error unless the IF NOT EXISTS option is used. If you use the option, the statement will be a no-op if the index already exists. Optionally, specify a name for the index itself before the ON keyword. Enclose a single column name in parentheses. It is not necessary for the column to exist on any current rows. The column and its data type must be specified when the table is created, or added afterward by altering the table.

You can use dot notation to specify a keyspace for the table: keyspace name followed by a period followed the name of the table. Apache Cassandra™ creates the table in the specified keyspace, but does not change the current keyspace; otherwise, if you do not use a keyspace name, Cassandra creates the index for the table within the current keyspace.

If data already exists for the column, Cassandra indexes the data during the execution of this statement. After the index is created, Cassandra indexes new data for the column automatically when new data is inserted.

Cassandra supports creating an index on most columns, including a clustering column of a compound primary key or on the partition (primary) key itself. Cassandra 2.1 and later supports creating an index on a collection or the key of a collection map. Cassandra rejects an attempt to create an index on both the collection key and value.

Indexing can impact performance greatly. Before creating an index, be aware of when and when not to create an index.

Counter columns cannot be indexed.

CREATE CUSTOM INDEX ON users (email) USING 'path.to.the.IndexClass';
CREATE CUSTOM INDEX ON users (email) USING 'path.to.the.IndexClass' WITH OPTIONS = {'storage': '/mnt/ssd/indexes/'};

Creating an index on a column

Define a table and then create an index on two of its columns:

CREATE TABLE myschema.users (
   userID uuid,
   fname text,
   lname text,
   email text,
   address text,
   zip int,
   state text,
   PRIMARY KEY (userID)
 );

CREATE INDEX user_state
   ON myschema.users (state);

CREATE INDEX ON myschema.users (zip);

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 mykeyspace.users (
   userID uuid,
   fname text,
   lname text,
   email text,
   address text,
   zip int,
   state text,
  PRIMARY KEY ((userID, fname), state)
);
      
CREATE INDEX ON mykeyspace.users (state);

Creating an index on a collection

In Cassandra 2.1 and later, create an index on a 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 phone numbers to the users table to index the data in the phones set.

ALTER TABLE users ADD phones set<text>;
CREATE INDEX ON users (phones);

If the collection is a map, Cassandra creates an index on map values. Assume the users table contains this map data from the example of a todo map:

{'2014-10-2 12:10' : 'die' }

The map value is located to the right of the colon, 'die'. The map key, the timestamp, is located to the left of the colon. You can also create an index on map keys using a slightly different syntax. If an index of the map keys of the collection exists, drop that index before creating an index on the map collection values.

Creating an index on map keys

In Cassandra 2.1 and later, 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.

CREATE INDEX todo_dates ON users (KEYS(todo));

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