SOQL Query Exception Non Selective Query in after insert Trigger

Question

I am loading contacts records and when my after insert triggers runs I am getting the following error:

System.QueryException: Non-selective query against large object type (more than 200000 rows). Consider an indexed filter or contact salesforce.com about custom indexing.

Even if a field is indexed a filter might still not be selective when:

1. The filter value includes null (for instance binding with a list that contains null)
2. Data skew exists whereby the number of matching rows is very large (for instance, filtering for a particular foreign key value that occurs many times)

I have the debug log and this occurs when the following is happening

SELECT Id, OwnerId, LastName, AccountId, Email FROM Contact WHERE
(Email = :tmpVar1 OR Personal_Email__c = :tmpVar2) LIMIT 1

When this runs it actually returns no rows, which is fine I am just not sure how I can solve this problem as I need to execute this query. I do know that there are over 200,000 records in total in the object.

Answer 1

Even if a field is indexed a filter might still not be selective when:

1. The filter value includes null (for instance binding with a list that contains null)

From your debug logs, you're running this query:

SELECT Id, OwnerId, LastName, AccountId, Email FROM Contact WHERE
    (Email = :tmpVar1 OR Personal_Email__c = :tmpVar2) LIMIT 1

Since these are debug logs, it's difficult to tell what tmpVar1 and tmpVar2 are, but it's likely that one or both of them are null or contain null. Since this is a trigger, I'd hope these tmpVars are sets/lists and this is a SOQL IN query, so make sure when constructing those collections not to add null values for this particular operation.

Answer 2

From Salesforce's documentation found here:

You may find that the query in question needs to be more selective in the WHERE clause. According to the Salesforce standards & best practices - the where clause needs to subset 10% or less of the data.

Consider putting more filters on your WHERE clause(s) to reduce the number of records you're returning.