Which solution will improve the user experience in the MOST efficient way?
Perform a Scan operation on the Orders table. Provide a QueryFilter condition to filter to only the items where the OrderSource attribute is equal to the MobileApp value.
Create a local secondary index (LSI) with OrderSource as the partition key. Perform a Query operation by using MobileApp as the key.
Create a global secondary index (GSI) with OrderSource as the sort key. Perform a Query operation by using MobileApp as the key.
Create a global secondary index (GSI) with OrderSource as the partition key. Perform a Query operation by using MobileApp as the key.
Explanations:
A Scan operation examines every item in the table, which can be inefficient for a large dataset like 100,000 records. Using a QueryFilter does not improve performance since it still scans all records before filtering.
A local secondary index (LSI) requires a sort key, which is not applicable here since the Orders table does not have a sort key. Therefore, it cannot be created with OrderSource as the partition key.
A global secondary index (GSI) cannot use the OrderSource as a sort key because the query would need to be based on a partition key. In this case, OrderSource must be the partition key to efficiently retrieve records.
Creating a global secondary index (GSI) with OrderSource as the partition key allows for efficient queries to retrieve all records where OrderSource is equal to MobileApp. This optimizes performance by indexing the relevant attribute directly.
In my opinion, the answer is:
Create a global secondary index (GSI) with OrderSource as the partition key. Perform a Query operation by using MobileApp as the key.