1

We have over 100,000 contacts to query the system to get this error.

SOSL: System.QueryException: Non-selective query against large object type (more than 100000 rows)

However, this error is only expected if we use SOQL. We are using SOSL instead and expect to return one record with the below query:

FIND :tmpVar1 IN ALL FIELDS RETURNING 
    Contact(Id, FirstName, LastName, MailingStreet, AccountId 
WHERE (((LastName IN :tmpVar3 OR Name IN :tmpVar4) 
    AND mailingStreet IN :tmpVar5 
    AND (AccountId = :tmpVar6 OR AccountId = NULL OR Account.Name = 'Citizen')))
    AND MUSW__Confidential__c = FALSE)
  • tempvar1 is a string eg. 'test name'
  • tempvar3 and 4 is a list of 1 string eg. {'test name'}
  • tempvar5 is a list of null eg {null}
  • tempvar6 is a salesforce id

any ideas (using SOSL is a Salesforce recommended way to NOT get this error)

| improve this question | | | | |
  • Isn't this error expected only if you want to fetch the 100k records? I mean, you can query them. You just can't return them and store in a variable. Have you tried to execute your query using SOQL to see if the error is actually thrown to you? – Renato Oliveira Oct 13 '15 at 19:47
  • 1
    @RenatoOliveira - Do a search on that error and you will see it is all about selectivity and has do do with the "potential" to return or scan over a large number of object even if the query might only return 1 result – Eric Oct 13 '15 at 19:59
2

Changing the formatting on the SOSL query helps make things clearer:

FIND :tmpVar1 IN ALL FIELDS RETURNING 
    Contact(Id, FirstName, LastName, MailingStreet, AccountId 
                WHERE 
                    (
                        (
                            (LastName IN :tmpVar3 OR Name IN :tmpVar4) 
                            AND mailingStreet IN :tmpVar5 
                            AND (AccountId = :tmpVar6 OR
                                 AccountId = NULL OR
                                 Account.Name = 'Citizen')
                        )
                    )
                    AND MUSW__Confidential__c = FALSE
            )

Your RETURNING FieldSpec is a SOQL query in disguise. The WHERE conditionExpression is passing through the same Query Optimizer that SOQL does. As such it needs to meet the same selectivity requirements.

Try using the Developer Console to get the query plan. In particular, your query will have problems with Composite Joins and Unions. You may need to perform a few more focused queries and combine the results. Also, consider your parameters. If tempVar5 is an empty list, then the clause AND mailingStreet IN :tmpVar5 isn't adding any value.

See Also:

| improve this answer | | | | |
0

Add an indexed field to your soql where clause to make the query more efficient.

| improve this answer | | | | |
0

Please check if you have any deleted records on Contact and Account object in the Recycle Bin, Please delete them. If still issue persists then log a case with Salesforce.com support for the hard delete. Then modify the query as 'OR' filter is not a good filter. Try using the Indexed or SystemModStamp field in the Filter value as it is the best filter and run on different table on server.

Thanks.

| improve this answer | | | | |

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.