---
schema_version: "1"
id: 8cd637046c3971c792932e7c93e4c007d3563a0aaac1f1fbcd3cef71c30bcb19
title: Indexing with Nulls
collection: developer
version: current
locale: en-us
portal_url: https://docs.salesforce.com/developer/current/en-us/platform/salesforce-large-data-volumes-bp/guides/cce/ldv-deployments/ldv-deployments-case-studies-indexing-with-nulls.md
canonical_url: https://developer.salesforce.com/docs/platform/salesforce-large-data-volumes-bp/guide/ldv-deployments-case-studies-indexing-with-nulls.html
source_site: developer.salesforce.com
content_type: documentation
content_hash: 3f5b84b75fb9d2f74358ddccd5e28205ed1a233df29deff7df6d8113705889cb
products:
  - Best Practices for Deployments with Large Data Volumes
  - Platform Developer Content
---

# 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.

```soql
SELECT Name
FROM Object
WHERE Status__c = ''
```

Instead, you can create a formula called `Status_Value`.

```soql
Status_Value__c = IF(ISBLANK(Status__c), "blank", Status__c)
```

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

```soql
SELECT Name
FROM Object
WHERE Status_Value__c = 'blank'
```

This concept can be extended to encompass multiple fields.

```soql
SELECT Name
FROM Object
WHERE Status_Value__c = '' OR Email = ''
```

For more information about standard and custom indexed fields, see [Indexes](https://developer.salesforce.com/docs/atlas.en-us.salesforce_large_data_volumes_bp.meta/salesforce_large_data_volumes_bp/ldv_deployments_infrastructure_indexes.htm) 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](https://help.salesforce.com/s/articleView?id=000385213&type=1&language=en_US).