Salesforce Developer Soql Avoid Filtering On Formula


// Salesforce - Developer - SOQL - Best Practices
// Avoid filtering on formula fields or contact Salesforce

Formula fields are custom fields that can help you dynamically calculate the 
value of a field based on other fields, expressions, or values.  As you can 
with any other field, you can use formula fields to filter SOQL queries. For 
example, you might write a query such as:

SELECT Id, Name FROM Contact WHERE FormulaField__c = '10'

However, just because you can filter queries using a formula field doesn’t 
mean that you should.  By default, formula fields don’t have underlying 
indexes, so they require full scans to find target records. They also have 
to calculate values on the fly because actual values are not stored in the 
database. So when an object has a lot of records, queries that filter using 
a formula field can be painfully slow.

To get around this limitations involving formula fields, we can create work-
around.  For example, we might have created triggers or workflow rules to 
store formula field values in a separate custom field that we could index, 
and then built queries that filtered using the custom field instead of the 
formula field. That strategy works, but it requires overhead and is not 
intuitive to developers.

Since the Winter ’13 release, we have been able to contact 
Customer Support to create a custom index on a formula field, provided that 
the function that defines the formula field is deterministic.
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License