Local Secondary Indexes¶

How Local Secondary Index Queries Work¶

Lets explore the example above, first with Global Secondary Index (GSI) and then 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 the query requires two inter-node hops

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 are guaranteed to be on the same node.

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 underlying 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.

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.

When should you use a Local Secondary Index¶

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

More information¶

• Global Secondary Indexes

• CQL Reference - CQL Reference for Secondary Indexes

The following courses are available from ScyllaDB University:

• Materialized Views and Secondary Indexes

• Local Secondary Indexes