Query Exception : Non-selective query against large object type (Even with the correct way of handling)

Question

I am getting System.QueryException: Non-selective query against large object type (more than 200000 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 a 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) Exception while trying to convert a Lead.

The exception occurred in the last line of the below code,

    List<Opportunity> oppsToProcess = new List<Opportunity>();
    Set<String> prospectIds = new Set<String>();
    for (Opportunity opp: newOpps) {
        if (opp.ConId__c != null) {
            opp.Prospect_Id__c = opp.ConId__c;
            opp.ConId__c = null;
        }
        
        if (!opp.IsClosed && opp.Prospect_Id__c != null && opp.SAL_Date__c != null) {
            oppsToProcess.add(opp);
            prospectIds.add(opp.Prospect_Id__c);
        }
    }

    if (oppsToProcess.isEmpty()) { return; }

    Set<Id> contactIds = new Set<Id>();
    String soql = 'SELECT ' + String.join(leadFields, ', ') + ' FROM Lead WHERE ProspectId__c IN :prospectIds';
    Map<String, Lead> mapLead = new Map<String, Lead>();
    for (Lead lead: (List<Lead>)Database.query(soql)) {
    }

1. My org has 150000 Lead records only, In the worst case, it's less than 200000 record limit.
2. I have another concern about the null check (ConId__c, Prospect_Id__c are Text fields), But I have checked that the null and the empty ('') acts in the same way.

Did I miss anything over there in my code?

Answer 1

As per the following document

https://help.salesforce.com/articleView?id=000325257&type=1&mode=1

1. Get the SOQL query and replace the list of fields with the count() operator:

   SELECT Count() FROM Lead WHERE ProspectId__c IN :prospectIds

2. Check the number of active records in the table:

   Select count() from Lead

   #of records: totalLeads (for eg. take 200000)

   Threshold for standard indexes: 30% * 200000 (totalLeads) = 60000

   Threshold for custom indexes: 10% * 200000 (totalLeads) = 20000

3. The only filter in the WHERE clause is ProspectId__c, so we will only consider the filter on this field.

   SOQL query: Select count() from Lead where ProspectId__c IN :prospectIds

   For example if the above SOQL returns 30000 records

   #of records 30000 > 20000 (threshold for custom indexes)

   Is the used filter selective? NOT

4. If ProspectId__c is custom indexed, then it would be a selective filter if the total number of records returned by SOQL is less than 20000

For more understanding on Make SOQL query selective, check the above mentioned link

Answer 2

The issue had arisen because the ProspectId__c field was not indexed, So, If we run into a "Non-selective query against large object type" error when performing a SOQL query or it takes a long time to run, we may need to make it selective and if it filters by a Text field

1. We need to make it as External ID.
2. Or we can get the Salesforce support to make this field indexed from Salesforce.