Database
Query & Search Optimization Cheat Sheet
Database
Query Optimization and Selectivity Overview
When building queries, list views, and reports, it's best to create filter conditions that are selective so that Force.com scans only the rows necessary in the objects that your queries target. This best practice is especially important when your queries target objects containing more than one million records. Read on to learn how to follow this best practice, minimize your query response times, and optimize your database's overall performance.
Several things can affect the selectivity of a query filter's conditions. • Whether the field in the condition has an index • Whether the value in the condition is selective, relative to the total number of records in the object. These numbers determine the selectivity threshold, which the Force.com query optimizer uses to ensure that the most appropriate index, if any, drives each of your queries. • Whether the operator in the condition permits the use of available indexes
SOQL Fields with Database Indexes
Index Selectivity Conditions and Thresholds
Indexed Standard Fields, All Objects
Other Indexed Fields
Unary Condition: Standard Index
Unary Condition: Custom Index
AND Condition*
OR Condition
LIKE Condition
• Id
• Unique fields
• Name
• External ID fields
Force.com uses a standard index if the filter targets less than:
Force.com uses a custom index if the filter targets less than:
• 30% of the first million records
• 10% of the first million records
Force.com uses a composite index join if the filter targets less than:
Force.com uses a union if the filter targets less than:
• 15% of all records after the first million records
• 5% of all records after the first million records
• Twice the index selectivity thresholds for each filter
• The index selectivity thresholds for each filter
• Lookup fields
• 1 million total records
• 333,333 total records
• The index selectivity thresholds for the intersection of those fields
• The index selectivity thresholds for the sum of those fields
For conditions that don't start with a leading wildcard, Force.com tests the first 100,000 rows for selectivity.
* RecordType is always indexed for all standard objects that feature it, but not all standard objects feature this field.
* The Force.com query optimizer can detect date and number ranges, and treats their filters on the same field as a single, combined filter.
Query Optimization Resources
Index Selectivity Exceptions
• Best Practices for Deployments with Large Data Volumes (white paper)
• Filter operators:
• OwnerId • CreatedDate • SystemModstamp • RecordType* • Master-detail fields
For more information about retrieving records from a large volume of data—and doing so quickly and efficiently—see these related resources.
Filter conditions are unselective—and thus do not use available indexes—if they use any of the following operators.
• Force.com Apex Code Developer's Guide (guide)
»»not equal to
• Force.com Blogs: Engineering (blog posts)
»»contains »»does not contain
• How to Improve Listview Performance (Salesforce Knowledge article)
• Comparison operators (when used with text and text fields):
• In the Salesforce Help:
»»Less than ()
»»"Getting the Most Out of Filter Logic"
»»Less than or equal to (
Database
Query Optimization and Selectivity Overview
When building queries, list views, and reports, it's best to create filter conditions that are selective so that Force.com scans only the rows necessary in the objects that your queries target. This best practice is especially important when your queries target objects containing more than one million records. Read on to learn how to follow this best practice, minimize your query response times, and optimize your database's overall performance.
Several things can affect the selectivity of a query filter's conditions. • Whether the field in the condition has an index • Whether the value in the condition is selective, relative to the total number of records in the object. These numbers determine the selectivity threshold, which the Force.com query optimizer uses to ensure that the most appropriate index, if any, drives each of your queries. • Whether the operator in the condition permits the use of available indexes
SOQL Fields with Database Indexes
Index Selectivity Conditions and Thresholds
Indexed Standard Fields, All Objects
Other Indexed Fields
Unary Condition: Standard Index
Unary Condition: Custom Index
AND Condition*
OR Condition
LIKE Condition
• Id
• Unique fields
• Name
• External ID fields
Force.com uses a standard index if the filter targets less than:
Force.com uses a custom index if the filter targets less than:
• 30% of the first million records
• 10% of the first million records
Force.com uses a composite index join if the filter targets less than:
Force.com uses a union if the filter targets less than:
• 15% of all records after the first million records
• 5% of all records after the first million records
• Twice the index selectivity thresholds for each filter
• The index selectivity thresholds for each filter
• Lookup fields
• 1 million total records
• 333,333 total records
• The index selectivity thresholds for the intersection of those fields
• The index selectivity thresholds for the sum of those fields
For conditions that don't start with a leading wildcard, Force.com tests the first 100,000 rows for selectivity.
* RecordType is always indexed for all standard objects that feature it, but not all standard objects feature this field.
* The Force.com query optimizer can detect date and number ranges, and treats their filters on the same field as a single, combined filter.
Query Optimization Resources
Index Selectivity Exceptions
• Best Practices for Deployments with Large Data Volumes (white paper)
• Filter operators:
• OwnerId • CreatedDate • SystemModstamp • RecordType* • Master-detail fields
For more information about retrieving records from a large volume of data—and doing so quickly and efficiently—see these related resources.
Filter conditions are unselective—and thus do not use available indexes—if they use any of the following operators.
• Force.com Apex Code Developer's Guide (guide)
»»not equal to
• Force.com Blogs: Engineering (blog posts)
»»contains »»does not contain
• How to Improve Listview Performance (Salesforce Knowledge article)
• Comparison operators (when used with text and text fields):
• In the Salesforce Help:
»»Less than ()
»»"Getting the Most Out of Filter Logic"
»»Less than or equal to (
