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.
Querying involves the same steps, but they will query by the same partition key for both the index query and the base query
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.
Additionally, it’s perfectly legal to create several indexes on one table. Some of them may be local, some of them global, even if they refer to the same column.
Transcript
I quickly covered global secondary indexes
Now, local secondary indexes are conceptually similar they’re also
implemented on top of materialized views in. ScyllaDB, the important difference is
that local indexes guarantee that for each base partition the corresponding
materialized view partition that we use for indexing, will be available on
the same node and it’s achieved by creating a slightly different
materialized view underneath which uses the same partition key as we have in the
base table which ensures this collocation and then also stores the
indexed columns and the rest of the primary key. Querying a local index is
also quite similar, because the mechanism is basically the same but there’s
also one big difference and let’s as before see some images
The first step is virtually the same we simply receive a read request and
what happens next is also exactly the same as we’ve seen the case with global
indexes, we need to select the base primary Keys from the index and
then we need to select the base rows but this time it’s going to happen from
exactly the same node, not much profit yet because we still need to contact the
node twice, once for the index and once for base rows but let’s take a look
at what happens if we use a token aware driver which is going to send
the data precisely to the node that holds it, here the query
arrives and as before we need to select the base primary
keys from the index and then fetch the rows that were interested in but this
time it happens on exactly the same nodes and that means that there’s no
redundant internode communication, everything is local, hence the name local
secondary indexes and we also benefit from local secondary
indexes when we write to a table with an index first of all the
query arrives, the insertion arrives and we write the data but then after we
generate a view update it’s also applied locally because the same node is
responsible for both the base partition and the view partition
Now we have local indexes, global indexes and can we combine them in a single table – yes
It’s perfectly legal to create several indexes on one table, some of them may be
local, some of they may be global, even if they refer to the same column and the
question is when we have a query which index will be used was the heuristics behind it
In query number one we simply select everything from the
table but want to fetch only dishes with an appropriate name so in
this scenario global index will be used. Why? Because we didn’t specify a
partition for this query and in order to use local indexes we need to specify a
single partition that the query will operate on, so how about query number two
we provided a partition key restriction now, we provide that restriction
now but we still can’t use the local index and actually the query won’t even
run and that’s because first it needs to still use a global index
because we didn’t specify a single partition but instead an equality
relational partitions and then we need to filter the results because
we weren’t able to figure out which base partitions
were used. More on filtering later. And then theres’s square number three and
obviously this one is eligible for local indexes because otherwise this
slide would make no sense if you didn’t use that at all
this query is a great example of how queries that want to use
local indexes should look like, we specify a single partition by specifying
a partition key restriction that is only rows with ID number 7 and then we
specify a restriction on the index column.