Indexing with Nulls

Situation

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.

Solution

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.

1SELECT Name
2FROM Object
3WHERE Status__c = ''

Instead, you can create a formula called Status_Value.

1Status_Value__c = IF(ISBLANK(Status__c), "blank", Status__c)

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

1SELECT Name
2FROM Object
3WHERE Status_Value__c = 'blank'

This concept can be extended to encompass multiple fields.

1SELECT Name
2FROM Object
3WHERE Status_Value__c = '' OR Email = ''

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.