function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
lopezclopezc 

"Non-selective query against large object type (more than 100000 rows)"

 Hi,
I'm getting "Non-selective query against large object type (more than 100000 rows)" error when executing the following query: 

List<Lead> matchingLeads = [Select Id, Demo_Account__c, Email, Site_ID__c, Status, OwnerId, Date_Converted__c, LeadSource, Demo_Account_Status__c,MMB__c, Market_Update__c, Newsletter_Italy__c, FutureTechs__c 
			    from Lead 
			    where Status != 'Duplicate' 
			    and Email != null 
			    and Email IN :leadMap.KeySet()]; 

 

See printed the content of the leadMap.KeySet(): 
lead Map set: {asdsdf456frg@test.com} 
lead Map set size: 1 

Why is this failing though I filtered my query ? 
How you can see the leadMap doesn't contain any null value and I can't make email to be a "externalID" because this is a standard field. 

The strange thing is that we are doing successfully queries against large objects (ie: Contacts) all the time, so the exception is not always thrown. The same query above works sometimes with different leadMaps. 

Any ideas? Please help!


paul-lmipaul-lmi
the email field isn't indexed by default, at least, that's what they told me when i hit this issue on a different object.  contact support and have them create a custom index for you.


md1md1
We recently ran into the same problem and based on this and another community post for the same (http://forums.sforce.com/sforce/board/message?board.id=apex&thread.id=2882&view=by_date_ascending), we added an external id field called 'Indexer' to the query but with no results. The query was on the Case object to retrieve all new cases and our app stopped working when the number of cases went beyond 100000. We had a rough 2 days before the problem was solved. Here's the solution email we received:
 
"We've enabled a custom index for case status.  I've tested saving a new case in your org andthis appears to be working now.
Let me take a moment to explain the underlying error foryou.  Whenever an object has greater than100K records any query on that object must be "selective."  For a query to be selective it must haveenough indexed filters (where clauses) so that less than 10% of the records (inour example 10K) are returned before applying the limit statement.
The index we just set up is for all cases with statusnew.  So as long as less than 10% of yourcases have a status of new you're query is going to work.  Currently you have about 110K cases and about7K new cases.   Because 7K is less than11K (10% of 100K) this query is selective. However, if you insert a large number of new cases you may run into thiserror again.
Regarding your initial index, 'Indexer', the reason thatwasn't eliminating the error is that it didn't reduce the number of matchingrecords below the 10% needed for a query to qualify as non-selective."
 
I hope this helps others that run into the problem - yes, a picklist field like status CAN BE indexed, so ask SFDC for it, and while you're at it, vote for this idea so we know which fields are indexed by default or custom-indexed and which aren't:
http://ideas.salesforce.com/article/show/74416/Add_Column_to_indicate_which_fields_have_Custom_Indexes 
Message Edited by md1 on  21-11-2009 11:10 AM


jujubajujuba
Hi


 


We can eliminate Non - Selective query error by having externalid (Indexed field) in a where clause. As your query is referring to Standard field Status and Email which cannot made external id's from our end. We can rise a salesforce ticket for making them external ids at the backend.


 


Are try to add any look up field in the query. Look up fields are by default indexed. For example add " Campaign != Null " in the query. This will help you put down of the count of records through which this query is iterating. This will be helpful only if the campaign is not required field in your project.


 


Thanks!


gbu.varungbu.varun
Hi,
 
You can log a case to salesforce for this. You should you use indexed field in where clause or a unique field in the query.