An Orders DynamoDB table uses OrderID as the partition key (no sort key) and contains over 100,000 items. You need to efficiently retrieve all orders whose OrderSource attribute equals 'MobileApp'. What is the most efficient design to improve user experience?

The most efficient approach is to create a Global Secondary Index (GSI) with OrderSource as the partition key. This allows you to directly Query the GSI using MobileApp as the partition key value, retrieving all matching items quickly and efficiently. Performing a Scan on the main table is inefficient for large tables because it reads every item, consuming significant read capacity units (RCUs) and increasing latency, especially when only a small subset of data is needed. A QueryFilter applied to a Scan only filters results after the scan, not reducing the RCUs consumed. A Local Secondary Index (LSI) must share the same partition key as the base table. Since the requirement is to query by OrderSource, and OrderID is the base table's partition key, an LSI on OrderSource as the partition key is not possible. Creating a GSI with OrderSource as the sort key would still require a Scan operation on the GSI (or a Query with a partition key that doesn't exist for this use case) to find all items where OrderSource equals MobileApp, which is inefficient.