Sort Optimization on a Query

Situation

The customer had the following query.

SELECT Id,Product_Code__c
FROM Customer_Product__c
WHERE CreatedDate = Last_N_Days:3

The query was looking for all the records created in the last three days, but the amount of data in the object exceeded the threshold for standard indexes: 30% of the total records up to one million records. The query performed poorly.

Solution

The query was rewritten as:

SELECT Id,Product_Code__c
FROM Customer_Product__c
WHERE CreatedDate = Last_N_Days:3
ORDER BY CreatedDate LIMIT 99999

In this query, the threshold checks were not done, and the CreatedDate index was used to find the records. This kind of query returns a maximum of 99,999 records in the order that they were created within the last three days, assuming that 99,999 or fewer records were created during the last three days.

note: In general, when querying for data that has been added over the Last_N_Days, if you specify an ORDER BY query on an indexed field with a limit of fewer than 100,000 records, the ORDER BY index is used to do the query.