1

I have a SOQL query that selects all Accounts where a custom date field is null. Since querying for nulls can lead to full table scans, would it be more efficient to use a checkbox field instead and check if it's false? So instead of this...

[SELECT ID
 FROM Account
 WHERE Date_of_Event__c = null];

I used this...

[SELECT Id
 FROM Account
 WHERE Event_Occurred__c = false];

...would that be better? Or would it not make a difference?

1
  • I have additional selective filters on this query, but I wasn't sure if this non-selective filter would affect the selectivity of the query as a whole. This answered that question.
    – cndebelen
    Jul 25, 2017 at 19:07

2 Answers 2

4

It wouldn't make a difference. Null values are never indexed, and checkboxes are never indexed. Both will have the same abysmal performance and may time out or hit non-selective query errors. You'll want to use additional criteria, if possible, use a LIMIT statement, or in some other way limit the number of rows that need to be scanned.

1

The other way to do this is to ensure that Date_of_Event__c is Indexed, and replace all NULL Date values with a value of 01/01/1969 or some dummy value, that way the Indexed value lives in the Index Tree so you don't need to do a full table scan.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .