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