0

System.QueryException: Non-selective query against large object type (more than 200000 rows). Consider an indexed filter or contact salesforce.com about custom indexing.

From documentation:

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)

Identified the object is having more than 200,000 records and below is the method which is been affected with index field.

jj_sss_Question_And_Answer__c q = [select 
                                             id,
                                             jj_sss_Community__c,
                                             jj_sss_Community__r.jj_sss_Hero1__c,
                                             jj_sss_Community__r.jj_sss_Hero2__c,
                                             jj_sss_Community__r.jj_sss_Hero3__c,
                                             jj_sss_Status__c,
                                             jj_sss_Title__c,
                                             jj_sss_Body__c,
                                             jj_sss_Question_Posted_By__c,
                                             jj_sss_Question_Creation_Date__c,
                                             jj_sss_Feed_Item_Id__c,
                                             jj_sss_First_answer_received_date__c
                                           from jj_sss_Question_And_Answer__c 
                                           where jj_sss_Feed_Item_Id__c = :Comment.FeedItemId and jj_sss_Feed_Comment_Id__c = null];

Can anyone help resolve this issue?

Improve this question
1

Are jj_sss_Feed_Item_Id__c, jj_sss_Feed_Comment_Id__c indexed fields? You can retest the query by making them external Id which will index them. Also ensure they are not cross object formulas.

Additionally, are you sure your bind variables are assigning a value? If they are passing through null and your table has many rows where those values are null, you will also hit this issue.

If your table size is simply too massive you may need to consider other approaches to be able to query effectively, view the query optimiser cheatsheet.

Edit: As David below mentioned, skinny table will purely provide performance improvements in querying and will not bypass errors due to query being unselective

Improve this answer
  • jj_sss_Feed_Item_Id__c is indexed field and they are not cross object formula fields. – supriya patki Jul 29 '19 at 10:11
  • A skinny table will have no effect on query selectivity or lack thereof. – David Reed♦ Jul 29 '19 at 10:54
  • True; editing reply – aidan Jul 29 '19 at 11:15
  • as per the above query on where condition jj_sss_Feed_Comment_Id__c = null . for Eg: when we query on on this field for 10 records, 5 will have value and 5 will be null. As you mentioned in 2nd point, are u referring this scenario? This null check to exclude null values. these 2 are indexed but not via Unique or external. – supriya patki Jul 29 '19 at 11:27
  • If you extend those numbers out, if you have 2 million records with null in a table with 4 million rows, that part of the filter will target 50% of the entire table which will smoke the selectivity threshold. That's why maximum table size is important to know how to help solve your issue, as well. Additionally, if the comment Id is not indexed (only feeditemid was confirmed above) I am unsure how the optimizer behaves, as threshold calculations are only described for standard/custom indexed fields and you have two filter conditions ANDed together. This could also be the culprit. – aidan Jul 29 '19 at 11:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.