Requirement Summary:
DynamoDB table Orders
Partition key: OrderID
No sort key
100,000+ records
Need to efficiently retrieve all records where:
OrderSource = "MobileApp"
Must optimize for user experience (i.e., performance)
Evaluate Options:
Option A: Scan with FilterExpression
Inefficient for large tables
A Scan reads every item in the table and then applies a filter condition.
This is slow, expensive, and not scalable as the dataset grows.
Option B: Create a Local Secondary Index (LSI) with OrderSource
Invalid: LSIs require the same partition key (OrderID) as the base table.
Since OrderSource is not part of the primary key, and we want to query based on it, LSI won’t help.
Option C: Create a GSI with OrderSource as the sort key
Misuse of sort key: Querying by sort key alone is not allowed in DynamoDB.
You must specify a partition key in a Query.
This design is not valid for the use case.
Option D: Create a GSI with OrderSource as the partition key
BEST CHOICE: With a GSI on OrderSource, you can query efficiently for all items where OrderSource = "MobileApp".
DynamoDB GSIs allow an alternative access pattern for queries not supported by the base table schema.
GSI Design: https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/GSI.html
Querying a GSI: https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Query.html
Scan vs Query: https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-query-scan.html
