Join us at ScyllaDB LIVE, instructor-led NoSQL training sessions | June 18
Register now

Secondary Indexes In Depth

7 min to complete

Secondary Indexes are a table containing a copy of the key of a base table, with a single partition key corresponding to the indexed column.
Indexes are created for one main purpose: to allow querying by a column that is not a key. CQL tables have strict schemas that define which columns form a primary key, and fundamentally we should use these keys to extract data from the database. But, in practice, we may want to occasionally query by a different, regular column, or several of them. How to achieve that? One of the ways is to create an index on that column.
Global indexes used to be the only kind that ScyllaDB supported, so it may be known to you already under the name “secondary indexes”. They’re easy to use and conceptually simple – we just pick a column to be indexed, create an index, and it just works!
This session covers examples, uses cases, what happens under the hood when Indexes are used.
Local secondary indexes are conceptually similar to global indexes, but there’s one important difference – local indexes guarantee that for each base partition, the corresponding rows in the materialized view will end up on the same node. It’s achieved by using the same partition key in the underlying view as we use in the base table.
The tricky part is querying – not all queries are eligible for local secondary indexes. In order to be able to benefit from a local index, we must provide a partition key restriction as well. As a result, only a single view partition will be queried, and what’s more, the base rows we need to fetch are on the exact same node as the materialized view partition. Hence the name, *local* indexes.

The lecture talks about Token Aware drivers, you can learn more about that in this lesson.