Join us at ScyllaDB University LIVE, instructor-led training sessions | March 19
Register now

Global Secondary Indexes

4 min to complete

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 voila, it works – we can now query by the name column.
Behind the scenes, on index creation, ScyllaDB creates a materialized view which has the indexed column as its partition key (so we can query by that column), and also primary key columns from the base table.
Querying an index looks simple from the user’s perspective, but there’s some magic involved underneath. After the user requests rows with a condition on the indexed column, the materialized view is queried, so we can extract base primary keys that have the name column value as requested. Then, after receiving all the base primary keys, we can query the base table using these keys. The result is, as we expect, all rows that fulfill given conditions.
To sum up, querying – there are two subqueries involved – first we fetch base primary keys from the underlying materialized view, and then we use them to query the base table directly. The result is exactly what we expect – base rows that fulfill our restrictions

 

Transcript
fa-angle-up