Make Salesforce Platform SOQL Query Selective

The complexity of the SOQL query has a massive influence on whether a field is suitable for indexing. We strongly recommended reviewing the following materials to fully understand how standard and custom indexes are different, and how they're employed.

• Working with very large SOQL queries
• Database Query & Search Optimization Cheat Sheet
• Force.com Query Optimizer FAQ
• Maximizing the performance of Force.com SOQL, reports and list views
• Force.com SOQL Best Practices: nulls and formula fields
• Force.com SOQL Performance Tips: LastModifiedDate vs SystemModStamp

Keep in mind - While every effort has been made to ensure this article explained how the Salesforce SOQL query optimizer works at a high level, its behavior can change without notice to accommodate additional performance enhancements. Developers are advised to use the Query Plan view (available since Summer '14) in the Developer Console, to be able to tune unselective queries.

The performance of a SOQL will depend on the presence of a selective filter. If a SOQL query contains at least 1 selective filter, the query is said to be selective. If the SOQL query doesn't contain a selective filter, the query is said to be un-selective and will require a full table scan.

From the perspective of the query optimizer, a filter can be simple or composite. A simple filter would be each of the field expressions (<field> <operator> <value>) in a condition expression that uses the "AND" operator. In contrast, the result of joining 2 or more field expressions via the "OR" operator is a composite filter. 

• fieldExpression1 AND fieldExpression2 - shows 2 simple filters
• fieldExpression1 OR fieldExpression2 - is a composite filter
• fieldExpression1 AND (fieldExpression2 OR fieldExpression3) - shows 2 filters (one simple, and one composite).

Understanding Query Selectivity

• Determine if it has an index.
  • If the filter is on a standard field, it'll have an index if it is a primary key (Id, Name, OwnerId), a foreign key (CreatedById, LastModifiedById, lookup, master-detail relationship), and an audit field (CreatedDate,   SystemModstamp).
  • Custom fields will have an index if they have been marked as Unique or External Id
• If the filter doesn't have an index, it won't be considered for optimization.
• If the filter has an index, determine how many records it would return:
  • For a standard index, the threshold is 30 percent of the first million targeted records and 15 percent of all records after that first million. In addition, the selectivity threshold for a standard index maxes out at 1   million total targeted records, which you could reach only if you had more than 5.6 million total records.
  • For a custom index, the selectivity threshold is 10 percent of the first million targeted records and 5 percent all records after that first million.  In addition, the selectivity threshold for a custom index maxes out at 333,333 targeted records, which you could reach only if you had more than 5.6 million records.
• If the filter exceeds the threshold,it won't be considered for optimization.
• If the filter doesn't exceed the threshold, this filter IS selective, and the query optimizer will consider it for optimization.

Examples:

Indexing Strategy

To Custom Index a field - Create a new case and provide the affected SOQL query, and user Id that can be used for testing, as well as the values of any binding variables used. 
Custom indexes, when used improperly, can actually slow query results. It's best to create filter conditions that are selective so Force.com scans only the rows necessary in the objects your queries target. Force.com query optimizer doesn't use an index to drive queries containing  non-selective filter conditions, even if the fields those filter conditions reference already have indexes on them. 
 

Checklist of details to include in Support case for a custom index

Be sure that you include this information in your request, the reason why this information needs to be provided is outlined below. 

1. Fields to be indexed - Makes sure we know which fields to focus on and which fields to leave alone. 
2. SOQL query with bind values -  Including the fields as part of filter criteria. We may need a query which has a filter on Severity__c, if you have provide login access we can query some sample values for custom field Severity__c and we can run the query.
   • Select Id, Case_Number__c from Case where Severity__c = 'Critical' - Correct, here Critical is a bind value.
   • Select Id, Case_Number__c  from case where Severity__c = :severityLevel - Incorrect, we can't run this query
3.  Login access - With login access, we'll be able to replicate and understand the issue correctly.
4. Error messages/time stamps - Any error logs/time-stamps will help us in understanding the need for index.
5. Steps to replicate the error - ensures that we follow the same path you took so we can better understand the issue. 
6. Evaluate possibilities of adding additional filters - Sometimes, adding any additional filters to the query without impacting the functionality can resolve the issue without having to add an index. Where we can't index or where the index turns out to be negative, we recommend adding additional filters to the query to reduce the number of resulting records. If this isn't possible, provide the reason you won't be able to add additional filters so that we can check for additional possibilities.
7.  If information for steps 4 and 5 are not known, substitute them with justification into why this is needed as indexing a field without proper reasoning can result in a decrease in performance.

What happens if more than one simple filter is selective? The query optimizer will choose the one with lower cost to drive the execution plan of the query.

How do you know if a composite filter is selective? The optimizer will determine if each of the simple filters is selective, and if in aggregate the number of returned records doesn't exceed the thresholds mentioned above. If these conditions are met, the filter will be selective.