|CQL reference / CQL commands|
Define a new index on a single column of a table.
CREATE CUSTOM INDEX IF NOT EXISTS index_name ON keyspace_name.table_name ( column_name ) USING class_name
Restrictions: Using class_name is only allowed 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.
In the synopsis section of each statement, formatting has the following meaning:
A semicolon that terminates CQL statements is not included in the synopsis.
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 it is used, 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.
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 most columns, including a clustering column of a compound primary key or on the partition (primary) key itself. Cassandra does not yet support indexes on collections. 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.
Cassandra supports creating a custom index, which is for internal use and beyond the scope of this document.
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 then create an index on the partition key.
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);