Improve Performance of SOQL Queries using a Custom Index

Important Note:
While this article explains how the Salesforce SOQL query optimizer works at a high level, its behavior may 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 tune non-selective queries based on information provided by the optimizer when the query plan is generated. Note: Indexes can add cost to DML operations and the Query Optimizer may behave differently based on indexes, which may affect performance.

Selective SOQL Query Criteria

A query is selective when one of the query filters is on an indexed field and the query filter reduces the resulting number of rows below a system-defined threshold. Query performance improves when two or more filters in the WHERE clause meet these conditions.
The selectivity threshold is 10% of records for the first million records, and less than 5% of records after the first million, up to a maximum of 333,000 records. For indexed standard fields, the threshold may be higher. The selectivity threshold is subject to change.

Custom Index Considerations for Selective SOQL Queries

The following fields are indexed by default in Salesforce: primary keys (Id, Name, and Owner fields), foreign keys (lookup or master-detail relationship fields), audit dates (such as SystemModStamp), and custom fields marked as External ID or Unique.
Salesforce Support can add Custom Indexes upon request. When creating a case, include the SOQL query that has the field to be indexed as a filter in the WHERE clause, including any bind values. Review the Checklist in "Make SOQL query selective" before creating a Support case.
A Custom Index cannot be created on multi-select picklists, currency fields in a multicurrency organization, long text fields, or binary fields (blob, file, or encrypted text). New complex data types added to Salesforce may also not allow custom indexing.
A custom index is typically not used in these cases: when the queried value exceeds the selectivity threshold, when the filter operator is a negative operator (NOT EQUAL TO, NOT CONTAINS, NOT STARTS WITH), when the CONTAINS operator is used and the rows to scan exceed 333,000, or when comparing with an empty value.

Examples of Selective SOQL Queries

Query 1 — Selective (uses index): A SOQL query filtering on the Account Id field — an indexed primary key — using an IN clause with a small list of IDs. Because the result count stays well below the selectivity threshold, Salesforce uses the index and performance is good.

SELECT Id FROM Account WHERE Id IN (<list of account IDs>)

Query 2 — Non-selective (does not use index): A query on Account filtering with Name != '' returns nearly all records. Even though Name is an indexed field, this filter is non-selective because it matches almost every record in a large object.

SELECT Id FROM Account WHERE Name != ''

Query 3 — Conditionally selective: A query combining two filters on Account: one non-selective filter (Name != '') and one potentially selective custom field filter (CustomField__c = 'ValueA'). If the second filter returns fewer records than the selectivity threshold and the field is indexed, the query is selective.

SELECT Id FROM Account WHERE Name != '' AND CustomField__c = 'ValueA'

Query 4 — Formula field indexing rules: A formula field can be indexed only if it references fields from a single object only (no relationship fields), does not reference non-deterministic functions such as SYSDATE, does not reference DATEVALUE() on a Date/Time field, does not reference primary keys (Id), does not use the TEXT() function on a picklist, and — if it references a lookup field — that field must not clear the value when the lookup record is deleted.

SELECT Id FROM Account WHERE FormulaField__c = 'ValueA'