ScyllaDB Docs ScyllaDB Manual CQL Reference Global Secondary Indexes

Caution

You're viewing documentation for an unstable version of ScyllaDB Manual. Switch to the latest stable version.

Global Secondary Indexes¶

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 [ CUSTOM ] INDEX [ IF NOT EXISTS ] [ `index_name` ]
                      :     ON `table_name` '(' `index_identifier` ')'
                      :     [ USING `string` [ WITH `index_properties` ] ]
index_identifier: `column_name`
                :| ( FULL ) '(' `column_name` ')'
index_properties: index_property (AND index_property)*
index_property: OPTIONS = `map_literal`
              :| view_property

where view_property is any property that can be used when creating a materialized view. The only exception is CLUSTERING ORDER BY, which is not supported by secondary indexes.

If the statement is provided with a materialized view property, it will not be applied to the index itself. Instead, it will be applied to the underlying materialized view of it.

For instance:

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

-- Create a secondary index called `catsIndex` on the table `Animals`.
-- The indexed column is `cats`. Both properties, `comment` and
-- `synchronous_updates`, are view properties, so the underlying materialized
-- view will be configured with: `comment = 'everyone likes cats'` and
-- `synchronous_updates = true`.
CREATE INDEX catsIndex ON Animals (cats) WITH comment = 'everyone likes cats' AND synchronous_updates = true;

-- Create a secondary index called `dogsIndex` on the same table, `Animals`.
-- This time, the indexed column is `dogs`. The property `gc_grace_seconds` is
-- a view property, so the underlying materialized view will be configured with
-- `gc_grace_seconds = 13`.
CREATE INDEX dogsIndex ON Animals (dogs) WITH gc_grace_seconds = 13;

-- The view property `CLUSTERING ORDER BY` is not supported by secondary indexes,
-- so this statement will be rejected by Scylla.
CREATE INDEX bearsIndex ON Animals (bears) WITH CLUSTERING ORDER BY (bears ASC);

View properties of a secondary index have the same limitations as those imposed by materialized views. For instance, a materialized view cannot be created specifying gc_grace_seconds = 0, so creating a secondary index with the same property will not be possible either.

Example:

-- This statement will be rejected by Scylla because creating
-- a materialized view with `gc_grace_seconds = 0` is not possible.
CREATE INDEX names ON clients (name) WITH gc_grace_seconds = 0;

-- This statement will also be rejected by Scylla.
-- It's not possible to use `COMPACT STORAGE` with a materialized view.
CREATE INDEX names ON clients (name) WITH COMPACT STORAGE;

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

Vector Index ScyllaDB Cloud¶

Note

Vector indexes are supported in ScyllaDB Cloud only in clusters that have the Vector Search feature enabled. Vector indexes do not support all ScyllaDB features (e.g., tracing, TTL, paging, and grouping). More information about Vector Search is available in the ScyllaDB Cloud documentation.

ScyllaDB supports creating vector indexes on tables, allowing queries on the table to use those indexes for efficient similarity search on vector data.

The vector index is the only custom type index supported in ScyllaDB. It is created using the CUSTOM keyword and specifying the index type as vector_index. Example:

CREATE CUSTOM INDEX vectorIndex ON ImageEmbeddings (embedding)
USING 'vector_index'
WITH OPTIONS = {'similarity_function': 'COSINE', 'maximum_node_connections': '16'};

The following options are supported for vector indexes. All of them are optional.

Option Name	Description	Default Value
`similarity_function`	The similarity function to use for vector comparisons. Supported values are: `COSINE`, `EUCLIDEAN`, and `DOT_PRODUCT`. `DOT_PRODUCT` requires vectors to be normalized, meaning each vector should have unit length (L2 norm = 1). For more information, see Vector normalization.	`COSINE`
`maximum_node_connections`	The maximum number of connections per node in the HNSW graph. In other HNSW implementations it is often denoted as `m`. Higher values lead to better recall (i.e., more relevant results are found) but increase memory usage and index size. Supported values are integers between 1 and 512.	`16`
`construction_beam_width`	The beam width to use during index construction. In other HNSW implementations it is often denoted as `efConstruction`. Higher values lead to better recall (i.e., more relevant results are found) but increase index creation time and memory usage. Supported values are integers between 1 and 4096.	`128`
`search_beam_width`	The beam width to use during index search. In other HNSW implementations it is often denoted as `efSearch`. Higher values lead to better recall (i.e., more relevant results are found) but increase query latency. Supported values are integers between 1 and 4096.	`64`
`quantization`	The quantization method to use for compressing vectors in Vector Index. Vectors in base table are never compressed. Supported values (case-insensitive) are: `f32`: 32-bit single-precision IEEE 754 floating-point. `f16`: 16-bit standard half-precision floating-point (IEEE 754). `bf16`: 16-bit “Brain” floating-point (optimized for ML workloads). `i8`: 8-bit signed integer. `b1`: 1-bit binary value (packed 8 per byte).	`f32`
`oversampling`	A multiplier for the candidate set size during the search phase. For example, if a query asks for 10 similar vectors (`LIMIT 10`) and `oversampling` is 2.0, the search will initially retrieve 20 candidates. This can improve accuracy at the cost of latency. Supported values are floating-point numbers between 1.0 (no oversampling) and 100.0.	`1.0`
`rescoring`	Flag enabling recalculation of similarity scores with full precision and re-ranking of the candidate set. Valid only for quantization below `f32`. Supported values are: `true`: Enable rescoring. `false`: Disable rescoring.	`false`

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.

Additional Information¶

The following courses are available from ScyllaDB University:

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?