+ Start a Discussion
KrisztianKrisztian 

Why is non-selective query?

I would like to ask you, the below SOQL query why is non-selective query?

SELECT Id, Name, E_mail__c, E_mail_2__c, Phone, mobilephone__c, Telefhone_3__c, Description, OwnerId, Owner.UserRole.Name
FROM Account
WHERE EXT_ID__c = '1' AND (E_mail__c = :var1 OR E_mail_2__c = :var1)

The total count of Accounts is 101000.
The count of Accounts where ext_id__c = '1' is 99000.
The count of Accounts where ext_id__c = '2' is 2000.
The EXT_ID__c is external id, not requied and not unique.
The var1 is not nullable.

If I filter ext_id__c to '1', it throws QueryException, but if '2' not throw.

Apex trigger Web2Lead caused an unexpected exception, contact your administrator: Web2Lead: 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): Class.Web2LeadHandler.RealtedAccount: line 190, column 1
Best Answer chosen by Krisztian
Tony TannousTony Tannous
Hello ,

try to use Soql For Loop and also verify that Var1 is not empty or  null

for(list<Account> listAccount :[SELECT Id, Name, E_mail__c, E_mail_2__c, Phone, mobilephone__c, Telefhone_3__c, Description, OwnerId, Owner.UserRole.Name
FROM Account
WHERE EXT_ID__c = '1' AND (E_mail__c = :var1 OR E_mail_2__c = :var1)])
{

for(Account acc :listAccount)
{
// put your logic here
}

// in case there is a dml statement put it here .
}

Good Luck

All Answers

Nilesh Jagtap (NJ)Nilesh Jagtap (NJ)
Hi,

Even though you are using External Id which will be a indexed field but when you use it with value as '1' there might be a larg threshhold of records which makes this query non-selective.

Let me give you an example,

Name field is a standard Indesed field for account but when you have millions of account in you system and you query is like
SELECT Id FROM Account WHERE Name != ''
The query becomes non-selective even if we are using indexed field in where clause.

Where as if we modify same query with
SELECT Id FROM Account WHERE Name = 'ABC'
It becomes selctive and returns records based on comparatively shorter threshold.

I would suggest to add one more indexed field in where clause to reduce search threshold for soql

Thanks,
N.J

Tony TannousTony Tannous
Hello ,

try to use Soql For Loop and also verify that Var1 is not empty or  null

for(list<Account> listAccount :[SELECT Id, Name, E_mail__c, E_mail_2__c, Phone, mobilephone__c, Telefhone_3__c, Description, OwnerId, Owner.UserRole.Name
FROM Account
WHERE EXT_ID__c = '1' AND (E_mail__c = :var1 OR E_mail_2__c = :var1)])
{

for(Account acc :listAccount)
{
// put your logic here
}

// in case there is a dml statement put it here .
}

Good Luck
This was selected as the best answer