Secondary Indexes

Experimental in Scylla 2.1-2.3, production ready in Scylla 3.0

CQL supports creating secondary indexes on tables, allowing queries on the table to use those indexes. A secondary index is identified by a name defined by:

index_name ::=  re('[a-zA-Z_0-9]+')

CREATE INDEX

Creating a secondary index on a table uses the CREATE INDEX statement:

create_index_statement ::=  CREATE INDEX [ index_name ]
                                ON table_name '(' index_identifier ')'
                                [ USING string [ WITH OPTIONS = map_literal ] ]
index_identifier       ::=  column_name
                           | ( KEYS | VALUES | ENTRIES | FULL ) '(' column_name ')'

For instance:

CREATE INDEX userIndex ON NerdMovies (user);
CREATE INDEX ON Mutants (abilityId);
CREATE INDEX ON users (keys(favs));

The CREATE INDEX statement is used to create a new (automatic) secondary index for a given (existing) column in a given table. A name for the index itself can be specified before the ON keyword, if desired. If data already exists for the column, it will be indexed asynchronously. After the index is created, new data for the column is indexed automatically at insertion time.

Indexes on Map Keys (supported in Scylla 2.2)

When creating an index on a maps, you may index either the keys or the values. If the column identifier is placed within the keys() function, the index will be on the map keys, allowing you to use CONTAINS KEY in WHERE clauses. Otherwise, the index will be on the map values.

DROP INDEX

Dropping a secondary index uses the DROP INDEX statement:

drop_index_statement ::=  DROP INDEX [ IF EXISTS ] index_name

The DROP INDEX statement is used to drop an existing secondary index. The argument of the statement is the index name, which may optionally specify the keyspace of the index.

Apache Cassandra Query Language