Error: System.QueryException: Non-selective query

Question

I get this error..

System.QueryException: Non-selective query against large object type (more than 100000 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)

on this line...

for(SUBSCR_SYSTEMS__c s:[select Account__c,CUST_NUM__c from SUBSCR_SYSTEMS__c where CUST_NUM__c!=null and CUST_NUM__c IN: shipTo])
{ }

I have researched a lot on working with large SOQL queries. To avoid non-selective error I added CUST_NUM__c!=null. I have converted the field CUST_NUM__c to a external ID field so that it is indexed. But I'm still unable to find a solution and get this error.

Any suggestions?

Answer 1

I feel like "CUST_NUM__c!=null" condition in your query is doing more harm than useful. Try running only second condition like below. But you have to make sure that you are not adding null values to the list/set "shipTo".

for(SUBSCR_SYSTEMS__c s:[select Account__c,CUST_NUM__c from SUBSCR_SYSTEMS__c where CUST_NUM__c IN: shipTo])
{ }

If you want to further explore how much selective is your query, you can enable "Query plan Tool" in developer console. It will help you to optimize your queries.

Answer 2

"not equals" clauses can't use an index. If your SOQL query had

WHERE CUST_NUM__c = '1234'

That would use the index you created on CUST_NUM__c

The (lousy) workaround for your case is to either use a dummy CUST_NUM__c (such as '999999'), or to create a separate text field that you keep updated via trigger to indicate that the record doesn't have an CUST_NUM__c value

Additionally, you should use the Query Plan Tool in the Dev Console to review the selectivity of your query:

https://help.salesforce.com/apex/HTViewSolution?id=000199003&language=en_US

Answer 3

we will receive an error message when a non-selective query in a trigger executes against an object that contains more than 100K records.

Selective SOQL Query Criteria

• The query filters should be on an indexed field
• The query filter reduces the resulting number of rows below a system-defined threshold.
• The selectivity threshold is 10% of the records for the first million records and less than 5% of the records after the first million records, up to a maximum of 333,000 records
• Two or more filters should used in the WHERE clause

another solution is, call soql from some other event not from trigger, for example if you call this from vf page this error will not come.