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

Question

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)

Answer 1

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:

• Query & Search Optimization Cheat Sheet
• Make SOQL query selective
• Developing Selective Force.com Queries through the Query Resource Feedback Parameter Beta

Answer 2

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

Answer 3

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.