[SalesForce] Why is this SOQL query not selective

We have following query in our code and its giving timeout error.

Select email, firstname, lastname, id 
From contact 
Where Email != Null 
And Opted_Out__c = false 
And id IN (Select contactid From CampaignMember Where campaignId = 'XXXXXXXXXXXX')

When we contacted SFDC support they said the query is not selective.

In SFDC documentation its mentioned that :

If the filter is on a standard field, it will have an index if it is a primary key (Id, Name, OwnerId), a foreign key (CreatedById, LastModifiedById, lookup, master-detail relationship), an audit field (CreatedDate, LastActivityDate, SystemModstamp).

In above example both inner and outer SOQL uses ID and so it should be selective query.

Can someone takeout time and explain me why its not Selective Query?

Best Answer

I think i found the reason why its not selective query.

When we use inner query SFDC cant use index. It doesnt matter whether we pass NULL into IN clause. If we just pass the list of IDS then query becomes selective and it works fine.

Here is is screen shot of my test.enter image description here

This blog helped me to reach to this conclusion.

Related Topic