Secondary 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.
These indexes are implemented on top of materialized views. It implies that there’s a storage overhead for creating an index – it will use another table to store the data it needs. Indexes can be global or local, and let’s find out what that means.
Transcript
Secondary indexes – secondary indexes were implemented for one main
purpose to allow querying by a column which is not a key. CQL tables have
strict schemas that define which columns form a primary key and which columns are
regular and fundamentally we should use these key columns to retrieve data from
the database, but in practice we may want to occasionally query by a
different column or several of them and how to achieve that if we simply try to
select by a column which is not the key we will get an error saying that this
query may be unpredictable in its performance so it’s not allowed. One of
the ways to allow such a query is to create an index our, example
table all over this presentation is going to be the table that represents
dishes in the restaurant chain, it consists of ID and location which
form a primary key for this table, name of the dish it’s price
What are secondary indexes? first of all secondary indexes in ScyllaDB
are implemented on top of materialized views and that implies first of all that
there’s storage overhead each index is going to be backed by a materialized
view and materialized view is a special kind of a table, also indexes, as of today
can be global or local and let’s find out what that means