Sort Optimization on a Query

Situation

The customer had the following query.

1SELECT Id,Product_Code__c
2FROM Customer_Product__c
3WHERE 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:

1SELECT Id,Product_Code__c
2FROM Customer_Product__c
3WHERE CreatedDate = Last_N_Days:3
4ORDER 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.

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.

Note