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.

Practice

1. What is the main purpose of a Local Secondary Index (LSI) in DynamoDB?

easy

A. To store data in a separate table

B. To allow querying data with the same partition key but different sort keys

C. To create a new partition key for faster queries

D. To backup data automatically

Solution

1. Step 1: Understand LSI functionality

   LSI lets you query items with the same partition key but sorted by an alternate sort key.

2. Step 2: Compare options

   Only To allow querying data with the same partition key but different sort keys correctly describes this feature; others describe unrelated features.

3. Final Answer:

   To allow querying data with the same partition key but different sort keys -> Option B

4. Quick Check:

   LSI = alternate sort key with same partition key [OK]

Hint: LSI changes sort key, not partition key [OK]

Common Mistakes:

• Thinking LSI creates new partition keys
• Confusing LSI with Global Secondary Index
• Believing LSI stores data separately

2. Which of the following is the correct way to define a Local Secondary Index (LSI) when creating a DynamoDB table?

easy

A. Define the LSI with a different partition key than the main table

B. Define the LSI with a new partition key and no sort key

C. Define the LSI after the table is created using updateTable API

D. Define the LSI with the same partition key but a different sort key

Solution

1. Step 1: Recall LSI definition rules

   LSIs must use the same partition key as the main table but can have a different sort key.

2. Step 2: Check options

   Only Define the LSI with the same partition key but a different sort key matches this rule; others violate partition key or timing constraints.

3. Final Answer:

   Define the LSI with the same partition key but a different sort key -> Option D

4. Quick Check:

   LSI = same partition key + different sort key [OK]

Hint: LSI shares partition key, differs in sort key [OK]

Common Mistakes:

• Trying to create LSI after table creation
• Using different partition keys for LSI
• Omitting sort key in LSI definition

3. Given a DynamoDB table with partition key UserID and sort key Timestamp, and an LSI with sort key OrderStatus, what will the following query return?

Query: KeyConditionExpression = UserID = '123' AND OrderStatus = 'Pending'

medium

A. All items with OrderStatus 'Pending' regardless of UserID

B. All items with UserID '123' sorted by Timestamp

C. All items with UserID '123' and OrderStatus 'Pending' sorted by OrderStatus

D. Syntax error because OrderStatus cannot be used in KeyConditionExpression

Solution

1. Step 1: Understand query with LSI

   Query on LSI uses same partition key but different sort key (OrderStatus). The query filters UserID = '123' and OrderStatus = 'Pending'.

2. Step 2: Analyze query result

   It returns items with UserID '123' and OrderStatus 'Pending', sorted by OrderStatus.

3. Final Answer:

   All items with UserID '123' and OrderStatus 'Pending' sorted by OrderStatus -> Option C

4. Quick Check:

   Query on LSI filters by partition and LSI sort key [OK]

Hint: LSI query filters by partition key + LSI sort key [OK]

Common Mistakes:

• Assuming LSI changes partition key
• Thinking query returns all items with OrderStatus only
• Believing syntax error occurs using LSI sort key

4. You created a DynamoDB table with an LSI but your query using the LSI's sort key returns no results even though matching items exist. What is the most likely cause?

medium

A. The partition key value in the query is incorrect

B. You used the LSI's partition key instead of the main table's partition key

C. The LSI's sort key attribute is missing in some items

D. The LSI was not defined at table creation time

Solution

1. Step 1: Check LSI query requirements

   Queries on LSI require the correct partition key value; wrong partition key returns no results.

2. Step 2: Evaluate options

   The partition key value in the query is incorrect explains no results due to incorrect partition key; other options are less likely or invalid.

3. Final Answer:

   The partition key value in the query is incorrect -> Option A

4. Quick Check:

   Correct partition key needed for LSI query [OK]

Hint: Always verify partition key value in LSI queries [OK]

Common Mistakes:

• Trying to add LSI after table creation
• Ignoring missing sort key attributes
• Confusing LSI partition key with main table partition key

5. You have a DynamoDB table with partition key CustomerID and sort key OrderDate. You want to query all orders for a customer sorted by OrderStatus instead of OrderDate. How should you design the Local Secondary Index (LSI) to achieve this?

hard

A. Create an LSI with partition key CustomerID and sort key OrderStatus defined at table creation

B. Create an LSI with partition key OrderStatus and sort key OrderDate after table creation

C. Create a Global Secondary Index (GSI) with partition key CustomerID and sort key OrderStatus

D. Add OrderStatus as a new partition key in the main table

Solution

1. Step 1: Understand LSI design rules

   LSI must use the same partition key as the main table but can have a different sort key.

2. Step 2: Apply to problem

   To sort by OrderStatus for the same CustomerID, create an LSI with partition key CustomerID and sort key OrderStatus at table creation.

3. Step 3: Eliminate wrong options

   Create an LSI with partition key OrderStatus and sort key OrderDate after table creation is invalid because LSI cannot be created after table creation; Create a Global Secondary Index (GSI) with partition key CustomerID and sort key OrderStatus is about GSI, not LSI; Add OrderStatus as a new partition key in the main table changes partition key, which is not allowed.

4. Final Answer:

   Create an LSI with partition key CustomerID and sort key OrderStatus defined at table creation -> Option A

5. Quick Check:

   LSI = same partition key + new sort key at creation [OK]

Hint: LSI must be defined at table creation with same partition key [OK]

Common Mistakes:

• Trying to create LSI after table creation
• Confusing LSI with GSI
• Changing partition key in LSI