Why secondary indexes enable flexible queries in DynamoDB - Visual Breakdown

Concept Flow - Why secondary indexes enable flexible queries

Start with main table query

↓

Check if query matches primary key

↓

Query main table

↓

Return results

↓

End

Queries first check if they can use the main table's primary key. If not, they use a secondary index to find data flexibly.

Execution Sample

DynamoDB

Query main table by primary key
If no match, query secondary index
Return matching items

This shows how a query tries the main table first, then uses a secondary index if needed.

Execution Table

Step	Query Target	Condition	Action	Result
1	Main Table	Query on primary key attribute	Check main table for matching items	Found 2 items
2	Main Table	Query on non-primary key attribute	No match on primary key	0 items found
3	Secondary Index	Query on non-primary key attribute	Check secondary index for matching items	Found 3 items
4	Secondary Index	Query on another attribute not in index	No matching index, no results	0 items found
5	End	No more options	Return results from last successful query	Query ends

💡 Query ends after checking main table and secondary index for matching items.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
Query Target	Main Table	Main Table	Main Table	Secondary Index	Secondary Index	Secondary Index
Items Found	0	2	0	3	0	3

Key Moments - 3 Insights

Why does the query check the main table first before using a secondary index?

Because the main table's primary key is the fastest way to find data. If the query matches the primary key, it returns results immediately (see execution_table step 1).

What happens if the query condition is on an attribute not in the primary key?

The main table query finds no items (step 2), so the query uses a secondary index that includes that attribute to find matching items (step 3).

Can a secondary index be used for any attribute?

No, only attributes included in the secondary index can be queried efficiently. If the attribute is not in the index, no results are found (step 4).

Visual Quiz - 3 Questions

Look at the execution table, how many items are found when querying the main table by primary key?

A 0 items

B 3 items

C 2 items

D 5 items

💡 Hint

Check execution_table row 1 under 'Result' column.

At which step does the query switch to using the secondary index?

A Step 3

B Step 1

C Step 2

D Step 4

💡 Hint

Look at the 'Query Target' column in execution_table to see when it changes to 'Secondary Index'.

If the attribute is not in any index, what is the result of the query?

A Returns items from secondary index

B Returns zero items

C Returns items from main table

D Returns all items

💡 Hint

See execution_table step 4 where no matching index means zero items found.

Concept Snapshot

Secondary indexes let you query data by attributes other than the primary key.
Queries first try the main table's primary key.
If no match, they use secondary indexes for flexible queries.
Only attributes in the index can be queried efficiently.
This enables faster, flexible data retrieval in DynamoDB.

Full Transcript

This visual execution shows how DynamoDB queries use secondary indexes to enable flexible queries. The query first tries the main table using the primary key. If the query condition matches the primary key, it returns results immediately. If not, the query tries a secondary index that includes the attribute being queried. If the attribute is in the secondary index, matching items are found and returned. If the attribute is not in any index, no items are found. This process allows queries on different attributes efficiently, enabling flexible data retrieval beyond the primary key.

Practice

1. What is the main benefit of using secondary indexes in DynamoDB?

easy

A. They allow querying data using different keys without changing the main table

B. They automatically backup your data

C. They increase the storage capacity of the table

D. They encrypt data for security

Solution

1. Step 1: Understand the role of secondary indexes

   Secondary indexes let you query the table using keys other than the primary key.

2. Step 2: Compare other options

   Options A, B, and C describe features unrelated to secondary indexes.

3. Final Answer:

   They allow querying data using different keys without changing the main table -> Option A

4. Quick Check:

   Secondary indexes = flexible queries [OK]

Hint: Secondary indexes = new query keys without table changes [OK]

Common Mistakes:

• Confusing secondary indexes with backups
• Thinking indexes increase storage size
• Assuming indexes encrypt data

2. Which of the following is the correct syntax to create a Global Secondary Index (GSI) in a DynamoDB table definition?

easy

A. {"IndexName": "GSI1", "KeySchema": [{"AttributeName": "Category", "KeyType": "RANGE"}], "Projection": {"ProjectionType": "KEYS_ONLY"}}

B. {"IndexName": "GSI1", "KeySchema": [{"AttributeName": "Category", "KeyType": "HASH"}], "Projection": {"ProjectionType": "ALL"}}

C. {"IndexName": "GSI1", "KeySchema": [{"AttributeName": "Category", "KeyType": "PRIMARY"}], "Projection": {"ProjectionType": "ALL"}}

D. {"IndexName": "GSI1", "KeySchema": [{"AttributeName": "Category", "KeyType": "SORT"}], "Projection": {"ProjectionType": "INCLUDE"}}

Solution

1. Step 1: Identify correct KeyType for GSI partition key

   For a GSI, the partition key uses KeyType 'HASH'. {"IndexName": "GSI1", "KeySchema": [{"AttributeName": "Category", "KeyType": "HASH"}], "Projection": {"ProjectionType": "ALL"}} uses 'HASH'.

2. Step 2: Check Projection type

   {"IndexName": "GSI1", "KeySchema": [{"AttributeName": "Category", "KeyType": "HASH"}], "Projection": {"ProjectionType": "ALL"}} uses 'ALL', which is valid. Other options use invalid KeyTypes like 'PRIMARY' or 'SORT'.

3. Final Answer:

   {"IndexName": "GSI1", "KeySchema": [{"AttributeName": "Category", "KeyType": "HASH"}], "Projection": {"ProjectionType": "ALL"}} -> Option B

4. Quick Check:

   GSI partition key = HASH [OK]

Hint: GSI partition key must be 'HASH' not 'RANGE' or others [OK]

Common Mistakes:

• Using 'PRIMARY' or 'SORT' as KeyType
• Confusing partition key with sort key
• Wrong ProjectionType values

3. Given a DynamoDB table with a Global Secondary Index (GSI) on attribute Category, what will the following query return?

Query({
  TableName: 'Products',
  IndexName: 'CategoryIndex',
  KeyConditionExpression: 'Category = :cat',
  ExpressionAttributeValues: { ':cat': 'Books' }
})

medium

A. All items where the primary key equals 'Books'

B. All items in the table regardless of Category

C. An error because the query does not specify the primary key

D. All items where the Category attribute equals 'Books' using the GSI

Solution

1. Step 1: Understand the use of IndexName in the query

   Specifying IndexName means the query uses the GSI on 'Category'.

2. Step 2: Analyze KeyConditionExpression

   The query filters items where 'Category' equals 'Books' using the GSI.

3. Final Answer:

   All items where the Category attribute equals 'Books' using the GSI -> Option D

4. Quick Check:

   Query with GSI filters by Category = 'Books' [OK]

Hint: Query with IndexName uses that index's keys [OK]

Common Mistakes:

• Assuming query filters primary key without IndexName
• Expecting error without primary key in main table
• Thinking query returns all items

4. You tried to query a DynamoDB table using a secondary index but got an error: ValidationException: Query condition missed key schema element. What is the most likely cause?

medium

A. You forgot to create the secondary index before querying

B. You used the primary key instead of the secondary index name

C. You did not specify the partition key attribute of the secondary index in the query

D. You used a Scan operation instead of Query

Solution

1. Step 1: Understand the error message

   The error means the query is missing a required key attribute from the index's key schema.

2. Step 2: Identify the missing key

   When querying a secondary index, you must specify its partition key attribute in the KeyConditionExpression.

3. Final Answer:

   You did not specify the partition key attribute of the secondary index in the query -> Option C

4. Quick Check:

   Missing index partition key causes ValidationException [OK]

Hint: Always include index partition key in query condition [OK]

Common Mistakes:

• Using primary key attributes instead of index keys
• Querying before index creation
• Confusing Scan with Query operation

5. You have a DynamoDB table with a primary key on UserId and a Global Secondary Index (GSI) on Email. You want to efficiently find users by their email address. Which approach best uses the GSI to achieve this?

hard

A. Query the table using the GSI with KeyConditionExpression 'Email = :emailValue'

B. Scan the entire table filtering items where Email equals the value

C. Query the table using the primary key UserId with the email value

D. Create a new table with Email as the primary key

Solution

1. Step 1: Identify the best way to find users by Email

   The GSI on Email allows querying by Email efficiently without scanning.

2. Step 2: Compare options

   Query the table using the GSI with KeyConditionExpression 'Email = :emailValue' uses the GSI query correctly. Scan the entire table filtering items where Email equals the value scans, which is slow. Query the table using the primary key UserId with the email value uses UserId, which won't help find by Email. Create a new table with Email as the primary key is unnecessary duplication.

3. Final Answer:

   Query the table using the GSI with KeyConditionExpression 'Email = :emailValue' -> Option A

4. Quick Check:

   Use GSI query for efficient Email lookup [OK]

Hint: Use GSI query, not scan or primary key, for alternate keys [OK]

Common Mistakes:

• Using Scan instead of Query on GSI
• Querying primary key with wrong attribute
• Creating redundant tables instead of indexes