Indexing with Nulls

The customer needed to allow nulls in a field and be able to query against them. Because single-column indexes for picklists and foreign key fields exclude rows in which the index column is equal to null, an index could not have been used for the null queries.

The best practice would have been to not use null values initially. If you find yourself in a similar situation, use some other string, such as N/A, in place of NULL. If you cannot do that, possibly because records already exist in the object with null values, create a formula field that displays text for nulls, and then index that formula field.

For example, assume the Status field is indexed and contains nulls.

Issuing a SOQL query similar to the following prevents the index from being used.

Instead, you can create a formula called Status_Value.

This formula field can be indexed and used when you query for a null value.

This concept can be extended to encompass multiple fields.

For more information about standard and custom indexed fields, see Indexes in Best Practices for Deployments with Large Data Volumes.

To avoid long execution times of SOQL queries, see Improve Performance of SOQL Queries using a Custom Index.