Scylla University Live | Nov 9 (AMERICAS), Nov 10 (EMEA and APAC) Free Virtual Training Event
Learn More
Menu

Local Secondary Indexes

Changed in version Scylla: Open Source 4.0

New in version Scylla: Enterprise 2020.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.

Note

As of Scylla Open Source 4.0, updates for local secondary indexes are performed synchronously. When updates are synchronous, the client acknowledges the write operation only after both the base table modification and the view update are written. This is important to note because the process is no longer asynchronous and the modifications are immediately reflected in the index. In addition, if the view update fails, the client receives a write error.

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.

More information

The following courses are available from Scylla University: