Local Secondary Indexes

New in version Scylla: 3.1

Local Secondary Indexes is an enhancement to Global Secondary Indexes, which allows Scylla to optimize workloads where the partition key of the base table and the index are the same key.

Example:

CREATE TABLE menus (location text, name text, price float, dish_type text, PRIMARY KEY(location, name));
CREATE INDEX ON menus((location),dish_type);

As the same partition key is used for the base table (menus) and the Index, one node holds both. When using a Token Aware Driver, the same node is likely the coordinator, and the query does not require any inter-node communication.

How Local Secondary Index Queries Work

Lets explore the example above, first with Global Secondary Index (GSI) and than with Local Secondary Index (LSI)

Global Secondary Index Example

CREATE TABLE menus (location text,
      name text, price float,
      dish_type text,
      PRIMARY KEY(location, name));

INSERT INTO menus (location, name, price, dish_type) VALUES ('Reykjavik', 'hakarl', 16, 'cold Icelandic starter');
INSERT INTO menus (location, name, price, dish_type) VALUES ('Reykjavik', 'svid', 21, 'hot Icelandic main dish');
INSERT INTO menus (location, name, price, dish_type) VALUES ('Da Lat', 'banh mi', 5, 'Vietnamese breakfast');
INSERT INTO menus (location, name, price, dish_type) VALUES ('Ho Chi Minh', 'goi cuon', 6, 'Vietnamese hot starter');
INSERT INTO menus (location, name, price, dish_type) VALUES ('Warsaw', 'sorrel soup', 5, 'Polish soup');


CREATE INDEX ON menus(dish_type);

The create Index does not include the base partition key. As a result, the following query will work, but in an inefficient manner:

SELECT * FROM menus WHERE location = 'Warsaw' and dish_type = 'Polish soup';

With GSI, dish_type acts as the partition key of the index table and query require two inter-node hops

../../_images/global-sec-index-example.png

GSI flow:

  • The user provides query details to the coordinator node (1)

  • An indexing subquery (2) is used to fetch all matching base keys from the materialized view.

  • The coordinator uses the resulting base key set to request appropriate rows from the base table (3).

Note, that partition keys from the base table and underlying materialized view are different, which means that their data is likely to be stored on different nodes.

Local Secondary Index Example Now let’s create an LSI, using the base table partition key, in this case location as partition key for the Index

CREATE INDEX ON menus((location), dish_type);
SELECT * FROM menus WHERE location = 'Warsaw' and dish_type = 'Polish soup';

The same query can be done to one node, as the Index and Base table partitions guaranteed to be on the same node.

../../_images/local-sec-index-example.png

LSI flow:

  • The user provides query details to the coordinator node (1)

  • An indexing subquery (2) is used to fetch all matching base keys from the underlyingy materialized view.

  • The coordinator uses the resulting base key set to request appropriate rows from the base table (3), located in the same node as the Index

Both the base table and the underlying materialized view have the same partition keys for corresponding rows. That means that their data resides on the same node and can thus be executed locally, without having to contact another node. When using a token aware policy, the entire query will be done with zero inter-node communication.

../../_images/local-sec-index-token-aware-exaple.png

LSI with Token Aware driver flow:

  • The user provides query details to the coordinator node (1)

  • The same(2) node:

    1. Act as the Coordinator

    2. Holds the Index

    3. Holds the base table

The coordinator processes the request for the index and base table internally and returns the value to the client with zero inter-node messaging.

Note

When the same table has both LSI and GSI, Scylla will automatically use the right Index for each query.

When should you use a Local Secondary Index

  • When your Index query includes the base table partition key.