Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed.
Fields that are indexed by default include:
- Primary keys: Id, Name, Owner, Email (contacts, leads)
- Foreign keys: lookup or master-detail relationships
- Audit dates: SystemModStamp, CreatedDate
- Custom fields: External ID (Auto Number, Email, Number, Text), Unique
- RecordTypeId & Division
Salesforce also supports custom indexes on custom fields, except for
- multi-select picklist
- text areas (long)
- text areas (rich)
- non-deterministic formula fields &
- encrypted text fields
The Salesforce multi-tenant architecture makes the underlying data table for custom fields unsuitable for indexing. To overcome this limitation, the platform creates an index table that contains a copy of the data, along with information about the data types. By default, the index tables do not include null records (records with empty values). You can work with Salesforce Customer Support to create custom indexes that include null rows.
Salesforce Query Optimizer
The Force.com query optimizer is an engine that sits between your SOQL, reports, and list views, and the database itself. Because of salesforce.com’s multitenancy, the optimizer gathers its own statistics instead of relying on the underlying database statistics. Using both these statistics and pre-queries, the optimizer generates the most optimized SQL to fetch your data. It looks at each filter in your WHERE clause to determine which index, if any, should drive your query.
Standard and Custom Indexed Fields
The query optimizer maintains a table containing statistics about the distribution of data in each index. It uses this table to perform pre-queries to determine whether using the index can speed up the query.
For example, assume that the Account object has a picklist field called Account_Type, which can take the value Large, Medium, or Small—and that the field has a custom index.
Salesforce generates a query like:
SELECT * FROM Account WHERE Account_Type__c = 'Large' ;
and then comes the role of query optimizer.The query optimizer performs a pre-query to its internal statistics table to determine the number of records with Large in the Account_Type field. If this number exceeds 10% of the object’s total records or 333,333 records, the query does not use the custom index. The query optimizer determines what an index is used with.
Standard Indexed Fields will be used if the filter matches less than 30% of the first million records and less than 15% of additional records, up to a maximum of one million records. The below picture will explain the different scenarios.
Custom Indexed Fields will be used if the filter matches less than 10% of the total records, up to a maximum of 333,333 records.
If the criteria for an indexed field are not met, only that index is excluded from the query. If they are in the WHERE clause and meet the thresholds for records, other indexes are sometimes used.
Index Selectivity Exception
There are few scenarios apart from the above that can prevent your SOQL queries from being selective.
Using leading wildcard %
when we use the leading wildcard in SOQL query then it will not be considered for indexing
select id from Account Where AccountType like ' %Large'
Use of NOT or != in query make query optimizer to not consider the index to drive the query even if you are using the index field.
References to Non-deterministic formula fields
Formula fields are custom fields that can help you dynamically calculate the value of a field based on other fields, expressions, or values.
You can use formula fields to filter SOQL queries. For example, you might write a query such as:
SELECT Id, Name FROM Contact WHERE FormulaField__c = '10';
By default, formula fields don’t have underlying indexes, so they require full scans to find target records.