Database
Database
Query & Search Optimization Cheat Sheet
Query Optimization Overview
When building queries, list views, and reports, it's best to create filter conditions that are selective so that Force.com scans the most appropriate rows in the objects that your queries target. This best practice is especially important when your queries target objects containing more than one million records. When writing SOQL, use the following fields to make your query filter conditions more selective, and improve your query response times and your database's overall performance.
Selectivity Overview
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 When writing your queries, remember the following selectivity conditions and thresholds.
SOQL Fields with Database Indexes
Index Selectivity Conditions and Thresholds
Primary Keys
Foreign Keys
Audit Dates
Custom Fields
Unary Condition: Standard Index
Unary Condition: Custom Index
AND Condition*
OR Condition
LIKE Condition
• Id
• CreatedById
• CreatedDate
• Unique fields
• Name
• LastModifiedById
• LastActivityDate
• OwnerId
• Lookup fields
• SystemModstamp
• 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
• 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. https://gus. salesforce. com/
• Master-detail relationship fields
*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
For more information about retrieving records from a large volume of data—and doing so quickly and efficiently—see these related resources. • Best Practices for Deployments with Large Data Volumes (white paper) • Force.com Apex Code Developer's Guide (guide) • Force.com Blogs: Engineering (blog posts) • How to Improve Listview Performance (Salesforce Knowledge article) • In the Salesforce Help: »»"Build Effective Filters" »»"Getting the Most Out of Filter Logic" »»"Improve Report Performance"
Index Selectivity Exceptions
When you build a filter condition with certain operators, Force.com doesn't use an available index. Instead, it scans all records in the object to find the records that satisfy the condition. When using the following operators, be sure to add selective filter conditions. • Filter operators: »»not equal to »»contains »»does not contain
• Comparison operators (when used with text and text fields): »»Less than ()
»»Less than or equal to (=)
Additionally, Force.com doesn't use available indexes when you use leading wildcards, or nondeterministic or cross-object formula fields.
SOSL Fields with Search Indexes
Search Selectivity Tips
• Name fields
• Be as selective as possible. For example, use Michael*, not Mich*.
• Phone fields • Text fields • Picklist fields These fields vary by object. See "Search Fields" in the Salesforce Help.
• Remember that Chatter feed searches aren't affected by the scope of your search, and their results include matches across all objects.
• Search for the exact phrase with an advanced search. • Limit scope by targeting: »»Specific objects »»Rows owned by the searcher »»Rows within a division, when applicable
See "Search Overview" in the Salesforce Help.
http://developer.force.com
Query & Search Optimization Cheat Sheet
Query Optimization Overview
When building queries, list views, and reports, it's best to create filter conditions that are selective so that Force.com scans the most appropriate rows in the objects that your queries target. This best practice is especially important when your queries target objects containing more than one million records. When writing SOQL, use the following fields to make your query filter conditions more selective, and improve your query response times and your database's overall performance.
Selectivity Overview
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 When writing your queries, remember the following selectivity conditions and thresholds.
SOQL Fields with Database Indexes
Index Selectivity Conditions and Thresholds
Primary Keys
Foreign Keys
Audit Dates
Custom Fields
Unary Condition: Standard Index
Unary Condition: Custom Index
AND Condition*
OR Condition
LIKE Condition
• Id
• CreatedById
• CreatedDate
• Unique fields
• Name
• LastModifiedById
• LastActivityDate
• OwnerId
• Lookup fields
• SystemModstamp
• 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
• 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. https://gus. salesforce. com/
• Master-detail relationship fields
*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
For more information about retrieving records from a large volume of data—and doing so quickly and efficiently—see these related resources. • Best Practices for Deployments with Large Data Volumes (white paper) • Force.com Apex Code Developer's Guide (guide) • Force.com Blogs: Engineering (blog posts) • How to Improve Listview Performance (Salesforce Knowledge article) • In the Salesforce Help: »»"Build Effective Filters" »»"Getting the Most Out of Filter Logic" »»"Improve Report Performance"
Index Selectivity Exceptions
When you build a filter condition with certain operators, Force.com doesn't use an available index. Instead, it scans all records in the object to find the records that satisfy the condition. When using the following operators, be sure to add selective filter conditions. • Filter operators: »»not equal to »»contains »»does not contain
• Comparison operators (when used with text and text fields): »»Less than ()
»»Less than or equal to (=)
Additionally, Force.com doesn't use available indexes when you use leading wildcards, or nondeterministic or cross-object formula fields.
SOSL Fields with Search Indexes
Search Selectivity Tips
• Name fields
• Be as selective as possible. For example, use Michael*, not Mich*.
• Phone fields • Text fields • Picklist fields These fields vary by object. See "Search Fields" in the Salesforce Help.
• Remember that Chatter feed searches aren't affected by the scope of your search, and their results include matches across all objects.
• Search for the exact phrase with an advanced search. • Limit scope by targeting: »»Specific objects »»Rows owned by the searcher »»Rows within a division, when applicable
See "Search Overview" in the Salesforce Help.
http://developer.force.com
