I have a function in which I query on Lead object and we have around 1 million leads. When we query on leads, we add a clause using a formula field in the query. for example,

[SELECT Id, Name, Status FROM Lead WHERE Standard_Phone__c='11234567890' LIMIT 1];

The Standard_Phone__c is a complex formula field thus, it cannot be indexed in Salesforce.

Is there any solution I can use to optimize the query so that it should minimize the number of records being traversed?

  • Are you able to add any other indexed fields to the WHERE clause so that your filter does not have to run on the entire data set? How complex is the formula field?
    – Z33dawg
    Mar 10 at 19:09
  • And what is the context of the code/logic around this query? Is it within a trigger context, scheduled job, etc. Mar 10 at 19:14
  • I recommend adding a trigger to normalize the phone number into an e.164 format and then making that field an external Id so it is indexed. Of course, you would need to backfill all your legacy Leads
    – cropredy
    Mar 10 at 22:35
  • @cropredy We can not afford to update all the leads in the system, it will be very risky as the instance have more than a million leads. Also, there are various triggers and field updates Mar 11 at 19:43
  • @KrisGoncalves The query is under Aura context. It's not being called from any asynchronous apex or triggers. Mar 11 at 19:45

I created an app, Unleash Your Formula, to deal with such a use case https://appexchange.salesforce.com/appxListingDetail?listingId=a0N4V00000DVyH4UAL. The app simply copies and syncs a formula field to a regular text (or other data type) field.

Simply install the app, create an indexed text field, map the formula field to this new field and query by this new field.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.