Salesforce Developer Soql Avoid Filtering On Nulls

salesforce-developer-soql

// Salesforce - Developer - SOQL - Best Practices
// Avoid filtering on nulls or contact Salesforce

We often need to query an object and find only the records in which a certain field 
is empty (i.e., null). For example:

SELECT Id, Name FROM Contact WHERE CustomField__c = null

That sounds simple enough, but when your query targets an object that has a lot of 
records, consider the performance implications. By default, underlying Force.com 
field indexes don’t include nulls. Therefore, queries such as the examples in this 
post require full scans, which again can be painfully slow.

Since the Winter ’13 release, you have been able to work with salesforce.com 
Customer Support to create custom indexes that include null rows. Even if you already 
have custom indexes on your custom fields, they need to be explicitly enabled and 
rebuilt to get the empty-value rows into index tables.

This option does not apply to picklists, external IDs, and foreign key fields. If you 
need to query on a null external ID field, you can work with salesforce.com Customer 
Support to create a two-column (compound) index instead.

Again, creating a custom index with null records does not alone guarantee that the 
platform will always make effective use of it, and general selectivity rules still 
apply. For best practices about filtering on fields with null, see Best Practices 
for Deployments with Large Data Volumes.
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License