CREATE INDEX

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

Synopsis 

CREATE CUSTOM INDEX index_name
ON keyspace_name.table_name ( 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. 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.

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.

In this release, Cassandra supports creating an index on a table having a compound primary key. You cannot create an index on the primary key itself. Cassandra does not support indexes on collections.

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

Examples 

Define a table and then create an index on two of its named 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);

Define a table having a compound primary key and create an index on it.

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