Local Secondary Index (LSI) concept in DynamoDB - Step-by-Step Execution

Concept Flow - Local Secondary Index (LSI) concept

Create Table with Partition Key

↓

Add Sort Key to Table

↓

Define LSI with Same Partition Key + Different Sort Key

↓

Insert Items into Table

↓

Query Table Using LSI

↓

Return Items Sorted by LSI Sort Key

LSI lets you query items with the same partition key but sorted by a different sort key defined at table creation.

Execution Sample

DynamoDB

CREATE TABLE Music (
  Artist STRING,
  AlbumTitle STRING,
  SongTitle STRING,
  Year NUMBER,
  PRIMARY KEY (Artist, AlbumTitle),
  LOCAL SECONDARY INDEX YearIndex (Artist, Year)
);

-- Query using LSI
SELECT * FROM Music WHERE Artist = 'Adele' ORDER BY Year;

Create a table with Artist as partition key and AlbumTitle as sort key, add LSI on Year, then query by Artist sorted by Year.

Execution Table

Step	Action	Input	Index Used	Output Rows	Notes
1	Create Table	Artist, AlbumTitle PK; Year LSI	None	Table created	Table with LSI defined
2	Insert Item	{Artist:'Adele', AlbumTitle:'25', SongTitle:'Hello', Year:2015}	None	Item stored	Stored with PK and LSI keys
3	Insert Item	{Artist:'Adele', AlbumTitle:'21', SongTitle:'Someone Like You', Year:2011}	None	Item stored	Stored with PK and LSI keys
4	Query Table	Artist='Adele'	YearIndex (LSI)	2 rows	Items returned sorted by Year (2011, 2015)
5	Query Table	Artist='Adele' ORDER BY AlbumTitle	Primary Index	2 rows	Items returned sorted by AlbumTitle
6	Query Table	Artist='Beyonce'	YearIndex (LSI)	0 rows	No items for this artist
7	End	No more queries	None	Execution stops	No more data to fetch

💡 No more queries or data to fetch, execution ends.

Variable Tracker

Variable	Start	After Insert 1	After Insert 2	After Query 1	After Query 2	Final
Table Rows	0	1 (Adele, 25, 2015)	2 (Adele, 21, 2011)	2 rows (Adele by Year)	2 rows (Adele by AlbumTitle)	2 rows

Key Moments - 3 Insights

Why does the LSI use the same partition key but a different sort key?

Because LSI allows querying items with the same partition key but sorted or filtered by a different attribute (sort key). See execution_table rows 4 and 5 where queries use the same Artist but different sort keys.

Can I add an LSI after the table is created?

No, LSIs must be defined when creating the table. This is shown in execution_table row 1 where the table is created with the LSI.

Why do queries using the LSI return sorted results by Year?

Because the LSI's sort key is Year, so querying via the LSI returns items sorted by Year automatically, as shown in execution_table row 4.

Visual Quiz - 3 Questions

Look at the execution_table, at which step are items returned sorted by the LSI sort key?

A Step 4

B Step 5

C Step 2

D Step 3

💡 Hint

Check the 'Index Used' and 'Output Rows' columns in execution_table row 4.

According to variable_tracker, how many rows are in the table after the second insert?

A 0

B 1

C 2

D 3

💡 Hint

Look at the 'After Insert 2' column for 'Table Rows' in variable_tracker.

If you query for Artist='Beyonce' using the LSI, what is the expected output?

A 2 rows

B 0 rows

C 1 row

D Error

💡 Hint

See execution_table row 6 for query results with Artist='Beyonce'.

Concept Snapshot

Local Secondary Index (LSI):
- Defined at table creation
- Uses same partition key as table
- Has different sort key
- Enables queries sorted by alternate attribute
- Useful for multiple query patterns on same partition key

Full Transcript

Local Secondary Index (LSI) in DynamoDB lets you query items sharing the same partition key but sorted by a different sort key defined when creating the table. You create a table with a partition key and a sort key, then define an LSI with the same partition key but a different sort key. When you insert items, they are stored with both keys. Queries using the LSI return items sorted by the LSI's sort key. LSIs must be defined at table creation and cannot be added later. This allows flexible querying on the same partition key with different sorting options.