function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Hari krishna 23Hari krishna 23 

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

Hi,

We are getting the bellow error in a trigger before  insert operation of a Task record. This trigger is trying to query the matching lead records witht the Phone number.Bellow is the query where its throwing the error.
Query:
for(lead l:[SELECT id,phone,Fini_Alternate_Phone__c from lead where IsConverted=false AND IsDeleted=false AND (phone IN: phoneNum OR Fini_Alternate_Phone__c IN: phoneNum)]){  
}
 
I am sure this query will not return more than 2 or 3 matching lead records. But still its giving the error.
ERROR:
Apex script unhandled trigger exception by user/organization: 

XXXXXXXXTrigger: execution of BeforeInsert

caused by: 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)

Trigger.XXXXXXXXX: line 37, column 1

Please help me to resolve this problem. Thanks 
Roy LuoRoy Luo
Likely the cause is item 1. The filter value includes null (for instance binding with a list that contains null)
so when you build your filter phoneNum set, only add not blank item to phoneNum.
Scott HungScott Hung
Another possibility is your variable phoneNum has SOQL wildcard search characters (e.g. ? or *).  This happened to me when I was using the results of one SOQL query in another and didn't realize the first SOQL query returned *.
Hari krishna 23Hari krishna 23
Yes we have a if condition to check the size of the set.
if(trec.ANI__c!=null) phoneNum.add(trec.ANI__c);
if(phoneNum.size()>0){            
                for(lead l:[SELECT id,phone,Fini_Alternate_Phone__c from lead where IsConverted=false AND IsDeleted=false AND (phone IN: phoneNum OR Fini_Alternate_Phone__c IN: phoneNum)]){                
                    if(l.phone!=null) leadPhonemap.put(l.phone, l);
                    if(l.Fini_Alternate_Phone__c!=null) leadPhonemap.put(l.Fini_Alternate_Phone__c, l);                
                }                                               
            }
 
Hari krishna 23Hari krishna 23
Hi Scott, yes, my query has SOQL wild cards. This field trec.ANI__c has both types of phone numbers like "(888) 888-8888, 8888888888". If my lead record has the formated phone num "(888) 888-8888" and if I am using the simple non formated phone# "8888888888" in query,then  SOQL not returning the lead record. So I have used both formated and non formated phone# in query. Is there a workarround to avoid this query type and to fix the issue. Thanks
 
Hari krishna 23Hari krishna 23
Hi ,

I have created formula fields to remove the formating of phone#, and updated the query accordingly.

if(trec.ANI__c!=null) phoneNum.add(trec.ANI__c);

if(phoneNum.size()>0){            
                for(lead l:[SELECT id,Un_Formatted_Alt_Phone__c,Un_Formatted_Phone__c from lead where IsConverted=false AND IsDeleted=false AND (Un_Formatted_Phone__c IN: phoneNum OR Un_Formatted_Alt_Phone__c IN: phoneNum)]){                
                    if(l.Un_Formatted_Phone__c!=null) leadPhonemap.put(l.Un_Formatted_Phone__c, l);
                    if(l.Un_Formatted_Alt_Phone__c!=null) leadPhonemap.put(l.Un_Formatted_Alt_Phone__c, l);                
                }                                              
            }

But still I am getting the same error.
Roy LuoRoy Luo
Print out the filter set and that should give you enough info to track down the issue. Then you may just format the soql to run at workbench: https://workbench.developerforce.com/query.php
 
if(trec.ANI__c!=null) phoneNum.add(trec.ANI__c);

if(phoneNum.size()>0)
{            
        for(String s : phoneNum)
        {
              System.debug(s);
        }                
                                                              
}