Non-selective query against large object type (more than 200000 rows) – Solution

apexselectivitysoql

Below is my query which throws the exception and is used in the trigger handler

SELECT Id, RecordTypeId, Status, customObj1__c FROM Case
WHERE customObj1__c IN : customObj1_Ids AND RecordTypeId IN : caseRecordTypes
  • customObj1_Ids is List of custom object Ids and
  • caseRecordTypes is set of case record types, predefined values

As per the help document if the fields in WHERE clause are standard indexes the query is selective. And when I look at this query, what we have used is Id fields, both of which are indexed.

What might have caused the issue then..? Making the method to Future or Moving the logic to a batch would help to resolve this..?

Best Answer

There are two culprits that will almost always be the issue. One is easy to fix, and that is if your collections of Id values may contain null, which will cause a table scan. Make sure you strip out all null values before executing your query.

As noted here:

Executing a query with a WHERE filter that has null values for picklists or foreign key fields doesn’t use the index, and should be avoided.

As mentioned in the comments, the other likely culprit is data skew, which is (in my experience) a bit less likely and significantly harder to fix.

Related Topic