Getting Exception in SOSL:Non-selective query against large object type (more than 200000 rows)

Question

I am using SOSL query in a method in Apex class.This method is getting called from trigger,when a custom object DCR is created.

SOSL Query:

List<List<Account>> acclist = [FIND :newstring IN NAME FIELDS RETURNING Account(Id,FirstName,LastName,Territory_vod__c where RecordType.Name='Prescriber' ) ];

DCR is having lookup with Account.newstring is having string value.I am using SOSL (instead of SOQL) due to requirement.I cannot use any other indexed field in WHERE condition due to requirement constraint.

I am getting this error while creating DCR record:

Non-selective query against large object type (more than 200000 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):

What could be the workarounds for this exception?

Answer 1

It appears your Account object has more than 200,000 records and the WHERE clause is not providing appropriate filtering.

According to the Salesforce standards & best practices - the where clause needs to subset 10% or less of the data.

You have three possible solutions, as per the article linked below:

1. You may find that the query in question needs to be more selective in the WHERE clause. According to the Salesforce standards & best practices - the where clause needs to subset 10% or less of the data.
2. A custom index on the field.

3. A possible quick fix may be to make the field in question an external ID. Since external IDs are indexed automatically, this will create the index and may solve the problem.

   • Go to Setup.
   • Under "Build", click Customize then click the object name.
   • Click Fields.
   • Then, edit field name and check the box External ID.

This article explains the problem and possible solutions.

Edit

If you must search through a huge amount of data then you should consider using OFFSET in your SOSL queries.

When expecting many records in a query’s results, you can display the results in multiple pages by using the OFFSET clause in a SOSL query.

You code would then look something like

List<List<Account>> acclist = [FIND :newstring IN NAME 
                               FIELDS RETURNING Account(Id,FirstName,LastName,Territory_vod__c WHERE RecordType.Name='Prescriber' OFFSET 200) 
                              ];

Keep in mind that the maximum OFFSET limit is 2000.

Answer 2

Error I was getting was because there were too many account records.This was solved by using with Sharing in the class.