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) {

    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?

  • 2
    To be sure, use the String.isNotBlank method instead of not null check.
    – Phil W
    Commented Jul 10, 2020 at 4:48
  • Talk to the support. We had an issue last time where we were filtering on the ID with non nulls and were getting the same error. We raised a case with Salesforce after verifying a lot of things and they fixed something on their end.
    – manjit5190
    Commented Jul 10, 2020 at 6:36
  • @manjit5190, Are you referring to the custom indexing for the field by conducting Salesforce Support? Commented Jul 11, 2020 at 6:28
  • 1
    No, the field we were using were already indexed and we were sure that the record ID using which we were querying was not null and we were supposed to get only one record. I dont know what support did but it got resolved. That may or may not be the case with you but its doesn't hurt to ask support.
    – manjit5190
    Commented Jul 11, 2020 at 9:00

2 Answers 2


As per the following document


  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

  • The query cannot return more than 10 records. The problem maybe because of the null check in the String fields. Commented Jul 10, 2020 at 5:33

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .