Apex Code Development You need to sign in to do that
Don't have an account?
h20rider Non-selective query against large object type (more than 100000 rows)
I am getting the message 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)
My query is as followed
Select Id, User__c, Role__c, Start_Date__c, End_Date__c, BFP_Account__c From BFP_Account_Team__c where BFP_Account__c in :BFPIds and Start_Date__c <= Today and (End_Date__c>=Today or End_Date__c = null)
BFP_Account__c is a lookup field and should be indexed. But I did notice that is some cases it did contain a null as one of the values. I am also filtering by End_Date__c = null
The question is if I get rid of the nulls in the BFPIds, can I still use End_Date__c = null? or do I have to find another way to replace End_Date__c = null
Yes lookups are already indexed internally. Do you have any custom indexing or external Id on any of the other filter fields (Start_Date__c, End_Date__c)? If so, avoid using any null or negative (NOT/!) criteria on those indexed fields. Otherwise the index will not be used by the query engine.
I understand the lookups are index. But I have seen in the forums that doesnt matter in the case of a null value.
In my case it is possible that I passed a null to the lookup value. In that case the BPF, which I saw nullifies the index. Also, I saw that if you searching in a null, that if you have another condition on a index field will rectify that issue.
So the question is, if I make sure no nulls are be query on the lookup field (BFP) , can I use a null on the end_date__c?
Reason I am asking is, that it would be a big change to our logic
Thanks!
I think the guideline is if you use null criteria, something like Indexed_field=null, then the index doesn't get used. If you're using an IN operator, I'm actually not sure if it matters to have a null value in your set. My sense is that it doesn't and the index is used regardless. And if you're diligent enough to get rid of all null values in your set, I'm pretty sure the index will be used. So yes, I think it's ok to use null on the end_date__c (giving up the index on that), as you'll get at least one index used for your query (for BPF).