Print this page

Improve SOQL Query Performance - Selectivity and Custom Indexes

Knowledge Article Number 000232453
Description
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. 
              
 
Resolution

Before creating a case

   

Details to include for a custom index request


Be sure that you include this information in your request, the reason why this information needs to be provided is outlined below. 
  • Fields to be indexed - Makes sure we know which fields to focus on and which fields to leave alone. 
  • 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.
  • Login accessWith login access, we'll be able to replicate and understand the issue correctly.
  • Error messages/time stampsAny error logs/time-stamps will help us in understanding the need for index.
  • Steps to replicate the error - ensures that we follow the same path you took so we can better understand the issue. 
  • Evaluate possibilities of adding additional filtersSometimes, 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.




promote demote