Indexes

Index Tables

The Salesforce multitenant 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.

The platform builds a standard database index on this index table. The index table places upper limits on the number of records that an indexed search can effectively return.

By default, the index tables don’t include records that are null (records with empty values). You can work with Salesforce Customer Support to create custom indexes that include null rows. Even if you already have custom indexes on your custom fields, you must explicitly enable and rebuild them to get the empty-value rows indexed.

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 field called Account_Type—which can take the value Large, Medium, or Small—and that the field has a custom index.

For example, Salesforce generates a query like:

SELECT *
FROM Account
WHERE Account_Type__c = 'Large'

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 doesn’t use the custom index.

The query optimizer determines what an index is used with.

Standard Indexed Fields

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.

For example, a standard index is used if:

• A query is executed against a table with 2 million records, and the filter matches 450,000 or fewer records.
• A query is executed against a table with 5 million records, and the filter matches 900,000 or fewer records.

Custom Indexed Fields

Used if the filter matches less than 10% of the total records, up to a maximum of 333,333 records.

For example, a custom index is used if:

• A query is executed against a table with 500,000 records, and the filter matches 50,000 or fewer records.
• A query is executed against a table with 5 million records, and the filter matches 333,333 or fewer records.

If the criteria for an indexed field aren’t 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.

The query optimizer uses similar considerations to determine whether to use indexes when the WHERE clause contains AND, OR, or LIKE.

• For AND, the query optimizer uses the indexes unless one of them returns more than 20% of the object’s records or 666,666 total records.
• For OR, the query optimizer uses the indexes unless they all return more than 10% of the object’s records or 333,333 total records.

  All fields in the OR clause must be indexed for any index to be used.

  Note

• For LIKE, the query optimizer doesn’t use its internal statistics table. Instead, it samples up to 100,000 records of actual data to decide whether to use the custom index.

Custom indexes can be created on deterministic formula fields. Because some values vary over time or change when a transaction updates a related entity, the platform can’t index non-deterministic formulas.

Here are examples of things that make formula fields non-deterministic.

SELECT Id
FROM Score__c
WHERE CrossObject1__r.CrossObject2__r.IndexedField__c

You can use this approach to replace formula fields that can’t be custom-indexed because they reference other objects. As long as the referenced field is indexed, the cross-object notation can have multiple levels.

Two-Column Custom Indexes

Two-column custom indexes are a specialized feature of the Salesforce platform. They’re useful for list views and situations where you want to use one field to select records to display and another field to sort them. For example, an Account list view that selects by State and sorts by City can use a two-column index with State in the first column and City in the second.

SELECT Name
FROM Account
WHERE f1__c = 'foo'
     AND f2__c = 'bar'