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`
                :| ( FULL ) '(' `column_name` ')'

For instance:

CREATE INDEX userIndex ON NerdMovies (user);
CREATE INDEX ON Mutants (abilityId);

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.

Local Secondary Index¶

Local Secondary Indexes is an enhancement of Global Secondary Indexes, which allows Scylla to optimize the use case in which the partition key of the base table is also the partition key of the index. Local Secondary Index syntax is the same as above, with extra parentheses on the partition key.

index_identifier: `column_name`
                :| ( PK ) | KEYS | VALUES | FULL ) '(' `column_name` ')'

Example:

CREATE TABLE menus (location text, name text, price float, dish_type text, PRIMARY KEY(location, name));
CREATE INDEX ON menus((location),dish_type);

More on Local Secondary Indexes

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.