3

I am receiving a System.QueryException when searching for contacts by phone number in an APEX class:

An error occurred at element myRule_1_A2 (FlowActionCall).
An Apex error occurred: 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) 

My query looks like this:

String phoneNumber = 55555555555
List<Contact> contacts = new List<Contact>();
contacts = [
    Select 
        Id, Name 
    FROM 
        Contact 
    WHERE 
        HomePhone = :phoneNumber OR MobilePhone = :phoneNumber                  
 ];

My organization has more than 100,000 contacts (actual number around 250,000). Can someone recommend a way of improving this query or bypassing the QueryException I am receiving.

  • 1
    A search of that error message will yield a lot of relevant results. – Eric Nov 25 '15 at 5:06
  • 2
    Definitely a lot of results, but not necessarily results showing me an alternative method. I find SF documentation to be relatively unclear much of the time (for example, the recent force:hasRecordId situation with communities that I recently came upon) – Andrew Jackman Nov 25 '15 at 6:05
7

You can try two things,

1) You can ask SFDC to index those fields, that would help.

2) Use SoSL. This may not apply to you but it may be worth a shot

Your query would be:

List<List<SObject>> searchList = [FIND :phoneNumber IN PHONE FIELDS Returning Contacts(Id, Name)];

List<Contact> contacts = (List<Contact>) searchList[0];

I would recommend option #2. I think you'll find that it works best to search.

=== Additional Info ===

1.A query is selective when one of the query filters is on an indexed field and the query filter reduces the resulting number of rows below a system-defined threshold. The performance of the SOQL query improves when two or more filters used in the WHERE clause meet the mentioned conditions.

  1. The selectivity threshold is 10% of the records for the first million records and less than 5% of the records after the first million records, up to a maximum of 333,333 records. In some circumstances, for example with a query filter that is an indexed standard field, the threshold may be higher. Also, the selectivity threshold is subject to change.

Reference: https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_VLSQ.htm

  • 2
    Note that since you're using an OR operator you'd need both phone fields indexed to be selective. See index unions – ca_peterson Nov 25 '15 at 7:11

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.