Friday, May 06, 2011

Salesforce : Non-selective query against large object type (more than 100,000 rows) - Solved

We are customizing SMS Magic package to enable feature for customer where he wants SMS's received
in inbox to be autolooked up in Contacts and should get attached to particular contact.

This is very easy in normal case.
You add lookup field on Incoming SMS object and lookup mobilenumber in Contacts using SOQL. and
assigns lookup field with matching Contacts's Id. I wrote the code and written test cases and everything was good
contactList = [select Id, OwnerId, Name from Contact where MobilePhone = :senderMobileNumber];

till I tried sending actual SMS to his org though SMS went thru to his inbox
but Contact lookup failed.

Now I was stuck. I enabled debug logs and tried to see whats happening and got this.

System.QueryException: Non-selective query against large object type (more than 100,000 rows) . Consider an index filter.

And I was clueless. I tried to search across salesforce forum but every one was stuck on it. I even told my colleague, this seems impossible lets refund client if we have charged him.
Someone suggested on discussion forum, that you need to add index using external_field but Contact is standard object so it was not possible.
then I tried adding another filter
contactList = [select Id, OwnerId, Name from Contact where MobilePhone != null and MobilePhone = :senderMobileNumber];

but thing didnt work.



but sometime later, I thought lets give a try with SOSL.
http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_sosl_examples.htm

I tried following :-

List<List<SObject >>searchList = [FIND :senderMobileNumber RETURNING Contact ];
contactList = ((List<Contact>)searchList[0]);
object.Contact__c = contactList[0].Id;
it worked beautifully without any issue.

Another solution for this as per Salesforce documentation is as follows:
http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_VLSQ.htm
I didnt try this solution. It should also work.