Question #746
A company has an ecommerce application. To track user orders, the company’s development team uses an Amazon DynamoDB table. Every record includes the following:
• An Order ID, a 16-digit universally unique identifier (UUID)
• A User ID and Product ID, 16-digit UUIDs that reference other tables
• An Order Date timestamp
• An optional shipping address
The table partition key is the Order ID. The most performed query against the table is to find the 10 most recent orders for a given user.
Which index will provide the FASTEST response for this query?
A global secondary index (GSI) with User ID as the partition key and Order Date as the sort key
A global secondary index (GSI) with User ID as the partition key and Order ID as the sort key
A local secondary index (LSI) with Order ID as the partition key and Order Date as the sort key
A local secondary index (LSI) with Order ID as the partition key and User ID as the sort key
Explanation
The fastest response is achieved using a Global Secondary Index (GSI) with User ID as the partition key and Order Date as the sort key (Option A). Here's why:
- Option A: A GSI with User ID as the partition key groups all orders by user. Using Order Date as the sort key allows queries to fetch the most recent orders by sorting in descending order (ScanIndexForward: false). This directly supports the requirement to retrieve the 10 most recent orders for a user.
- Option B: A GSI with User ID as the partition key and Order ID as the sort key cannot sort orders by date, as Order ID is a UUID unrelated to time. This makes it inefficient for date-based queries.
- Options C & D: Local Secondary Indexes (LSIs) require the same partition key as the base table (Order ID). Since the query requires filtering by User ID, LSIs cannot support this efficiently, as they cannot change the partition key.
Key Points:
1. GSIs allow a different partition key, enabling efficient queries on User ID.
2. Sorting by Order Date ensures the most recent orders are retrieved quickly.
3. LSIs are invalid here because they cannot change the base table's partition key.
Answer
The correct answer is: A