Local Secondary Indexes

Local secondary indexes are an enhancement to (global) secondary indexes. The difference between the two is that in the case of a local secondary index the corresponding rows in the index are guaranteed to end up on the same node. This results in very efficient queries if you filter by the indexed column(s).
Usage

Create table:

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

Create a local secondary index:

CREATE INDEX dish_sec_ix ON menus (
(location), dish_type
);

Query the table using the newly created index:
SELECT * FROM menus WHERE location = ‘Warsaw’ and dish_type = ‘Polish soup’;

Drop the index:
DROP INDEX dish_sec_ix;

When can you use local secondary indexes?
You can use local secondary indexes when you want to filter by one or multiple columns that are not part of the partition key.