Salesforce Object Query Language (SOQL) queries can be very practical if written efficiently. However, some queries can have extremely long execution times, especially when run on non-indexed fields. These are called non-selective queries. They are typically terminated by the system to to avoid the lengthy execution times. A query that must check an object with over 100,000 records will return an error message. In order to get the best performance times and avoid such error messages, selective queries should be used.
A selective query has at least one query filter that is on an indexed field and reduces the number of rows returned below the system threshold. When a field is indexed, its values are stored in a more efficient data structure. This takes up more space but improves performance when at least two filters with indexed fields are used in a query.
Fields that are indexed by default include:
Primary keys: Id, Name, Owner, Email (contacts,
Foreign keys: lookup or master-detail
- Audit dates: SystemModStamp, CreatedDate
- Custom fields: External ID (Auto Number, Email, Number, Text), Unique
If you would like to have another field indexed, you can use a custom index. Custom indexes are supported on standard and custom fields [excluding multi-select picklists, currency fields in a multicurrency organization, text area (long), text area (rich), non-deterministic formula fields, and binary fields (blob, file, encrypted text)].
It is also important to note that index tables do not include null records by default, even on custom indexes. This feature must be explicitly requested and enabled when working with Salesforce Support.
Custom indexes can be requested on both standard and custom fields by creating a case with Salesforce Support. You will need to provide your SOQL query that contains the field to be indexed in the WHERE clause and any bind values. See Checklist for SOQL Custom Index Requests for a full list of information to include in your request.