The following SOSL is in an Account trigger in a managed package:

/* Trigger is on API 27.0 */

String acctsNamesOrd = 'PARKER/VANGUARD MGT';
Map<Integer, String> acctsNames = new Map<Integer, String>{0 => 'PARKER/VANGUARD MGT'};
Map<Integer, String> acctsAddress = new Map<Integer, String>{0 => '15 COMMAND DR. #9'};
Map<Id, Account> acctsChanged = new Map<Id, Account>{'001J000001YAno5' => new Account(Id='001J000001YAno5')};

List<List<Sobject>> contactMatchSosl = [find :acctsNamesOrd in ALL FIELDS 
    returning Contact(Id, PKG__DBA__c, FirstName, LastName, MailingStreet, AccountId 
    where (PKG__DBA__c in :acctsNames.values() 
        or ((LastName in :acctsNames.values() or Name in :acctsNames.values()) and mailingStreet in :acctsAddress.values() and (AccountId in :acctsChanged.keySet() or Account.Name = 'Public'))) 
        and PKG__Confidential__c = false)];

When the package is installed in a client sandbox with 100K+ Contacts we get a QueryException BUT the exact same Search runs fine as part of a custom class: I created an Apex class (API 27.0) in client sandbox and copy/pasted the SOSL in there. Then called it up from Execute Anonymous. I have verified that all variables referenced in the packaged code are non-empty/non-null.

Not just that, the Search also runs fine in Query Editor (in client sandbox):

find {"PARKER/VANGUARD MGT"} in ALL FIELDS returning Contact(Id, PKG__DBA__c, FirstName, LastName, MailingStreet, AccountId where (PKG__DBA__c in ('PARKER/VANGUARD MGT') or ((LastName in ('PARKER/VANGUARD MGT') or Name in ('PARKER/VANGUARD MGT')) and mailingStreet in ('15 COMMAND DR. #9') and (AccountId in ('') or Account.Name = 'Public'))) and PKG__Confidential__c = false)

The Query Plan tool is showing these:

Cardinality | Fields | Leading Op Cost | sObject Cardinality | sObject Type
55206       |        | TableScan       | 118208              | Contact

Not considering filter for optimization because unindexed. Table: Contact Fields: ["IsDeleted"]
Not considering filter for optimization because query table is not optimizable. Table: Account Fields: ["IsDeleted"]
Not considering filter for optimization because unindexed. Table: Contact Fields: ["PKG__Confidential__c"]
Not considering ORed filter for optimization because unindexed. Table: Contact Fields: ["PKG__DBA__c"]

I am not understanding this discrepancy. What's going on?

UPDATE: I was able to repro the error using a custom trigger within the sandbox. As sfdcfox pointed out below running the code in Exec Anon is not the same thing.

  • Calling a query from a class from execute anonymous using an administrator is not the same as calling it from a trigger. You need to refactor your query so it runs with a leading Index cost instead of a table scan, or you'll run into that error only during trigger context. You may need to use either a SOQL instead, or remove some of the conditions from your initial SOSL and then perform a SOQL based on the results from the SOSL call.
    – sfdcfox
    Oct 29, 2015 at 22:29
  • This SOSL actually used to be a SOQL -until 12 months ago when we ran into the very same issue. That was when I switched to SOSL and it worked -until now... I just tried running the query inside a custom trigger in the client sandbox and, you were right, I got the error. I don't understand how this is not working anymore.
    – Mossi
    Oct 29, 2015 at 23:03
  • @sfdcfox Can you elaborate on why the execute anonymous admin vs trigger context makes a difference?
    – Keith C
    Oct 29, 2015 at 23:18
  • See Working with Very Large SOQL Queries, but basically they give a hint: For best performance, SOQL queries must be selective, particularly for queries inside of triggers. To avoid long execution times, the system can terminate nonselective SOQL queries. -- You need to make sure that queries are optimized; the rule is in place to make sure that row lock times are minimized.
    – sfdcfox
    Oct 29, 2015 at 23:33
  • I was able to identify the exact cause of the failure (with your help) BUT I have no idea how to do my Search. I can't split it into 2 Searches/Queries either; as long as PKG__DBA__c is in the filter I get the error. Remember that this is an OR Search, which means I can't loop through the results to narrow them down. Any ideas?
    – Mossi
    Oct 30, 2015 at 18:45

1 Answer 1


The error was caused by using a non-indexed field in an OR condition. In this case removing PKG__DBA__c in :acctsNames.values() fixed the issue.

It's also worth noting that having the above condition as the only criterion in the where-clause causes the same error. It seems having a non-indexed field alone or ORed is not advised when querying large data sets.

Here are some condition that I have tried (IX means indexed, NIX means non-indexed):

/* NIX ORed with IX ANDed with NIX - Errors */
WHERE (NIX in :values OR IX in :values) AND NIX = value

/* NIX ORed with IX - Errors */
WHERE (NIX in :values OR IX = 'value')

/* NIX alone - Errors */
WHERE (NIX in :values)

/* NIX ANDed with IX (not equals) - Errors */
WHERE (NIX in :values AND IX != 'value')

/* NIX ANDed with IX (equals) - Works */
WHERE (NIX in :values AND IX = 'value')

Also beware of the following discrepancy between the Query/Search Optimization Cheatsheet and this documentation: LastModifiedDate is not indexed but CreatedDate is.

I still don't have a solution on how to make my Search work though.

You must log in to answer this question.

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