Which index will provide the FASTEST response for this query?
A global secondary index (GSI) with Product ID as the partition key and Product Rating as the sort key
A global secondary index (GSI) with Product ID as the partition key and Review ID as the sort key
A local secondary index (LSI) with Product ID as the partition key and Product Rating as the sort key
A local secondary index (LSI) with Review ID as the partition key and Product ID as the sort key
Explanations:
A Global Secondary Index (GSI) with Product ID as the partition key and Product Rating as the sort key allows for quick retrieval of reviews for a product, sorted by rating. This structure directly supports querying the top 10 highest-rated reviews for a given product, which is the most frequent query.
A GSI with Product ID as the partition key and Review ID as the sort key will not optimize the query for the top-rated reviews. The Review ID is not relevant to sorting by rating, so it would require additional filtering or scanning, leading to slower performance.
A Local Secondary Index (LSI) with Product ID as the partition key and Product Rating as the sort key could work, but LSIs are limited to the same partition key as the base table (Review ID), and the query could only work effectively when querying for a single partition key. This may limit scalability and performance for larger queries.
An LSI with Review ID as the partition key and Product ID as the sort key would not be helpful. The query is based on Product ID and Product Rating, and this index would not provide the necessary sorting or filtering capabilities to efficiently retrieve the top-rated reviews for a product.
I systematize that the answer is:
A global secondary index (GSI) with Product ID as the partition key and Product Rating as the sort key