Was this page helpful?
Caution
You're viewing documentation for a previous version of ScyllaDB Open Source. Switch to the latest stable version.
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]+')
Creating a secondary index on a table uses the CREATE INDEX
statement:
create_index_statement: CREATE INDEX [IF NOT EXISTS] [ `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 Indexes is an enhancement of Global Secondary Indexes, which allows ScyllaDB 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
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.
The following courses are available from ScyllaDB University:
Copyright
© 2016, The Apache Software Foundation.
Apache®, Apache Cassandra®, Cassandra®, the Apache feather logo and the Apache Cassandra® Eye logo are either registered trademarks or trademarks of the Apache Software Foundation in the United States and/or other countries. No endorsement by The Apache Software Foundation is implied by the use of these marks.
Was this page helpful?