Will there be any performance impact when using SOSL with where clause?
There are millions of records in one of the custom object. We need to develop custom search functionality where user can select multiple fields and enter search txt against each field. We are having performance issue while using SOQL therefore wanted to switch to SOSL.
Basically I would like to know how where clause works in SOSL, whether it performs the full text search first then apply where clause on the result set?
As per the Developer Guide on SOSL WHERE clause, it is used to filter the search results:
By default, a SOSL query on an object retrieves all rows that are visible to the user. To limit the search, you can filter the search result by specific field values.
Furthermore, the syntax of the SOSL also suggests that the WHERE clause is applied to search results, yielded by the text search:
FIND {search_query} RETURNING sObject(fieldName WHERE fieldExpression)
Consider the following example:
FIND {John} IN NAME FIELDS RETURNING Account(Id, Name WHERE IsImportant__c=true), Contact(Id, FirstName, LastName WHERE IsPrimary__c=true)
In the example above, one would expect the search to be performed on all Accounts and Contacts, and the filter subsequently applied to the resulting record set (all Accounts and Contacts where term 'John' is found in Name fields), thus eventually returning only the records meeting the expression criteria (all Important Accounts and Primary Contacts where term 'John' is found), rather than initially filtering all accounts and contacts to important/primary ones and then doing a text search on those.
Considerations when using SOQL or SOSL:
Both SOSL search queries and SOQL WHERE filters can specify text to look for. When a given search can use either language, SOSL is generally faster than SOQL if the search expression uses leading wildcards or a CONTAINS term.
In some cases, when multiple WHERE filters are being used in SOQL, indexes cannot be used even though the fields in the WHERE clause may be indexed. In this situation, decompose the single query into multiple queries each with one WHERE filter and then combine the results.
Given the problem presented (users being able to select multiple fields and enter search text against each field), I assume you considered translating the search conditions into the WHERE condition in SOSL. Does not sound like a good idea, as you might end up with the following:
...RETURNING YourTargetObject(Id WHERE selectedField1 LIKE %textInput1% OR selectedField2 LIKE %textInput2%)
Apart from the above, the question arises what would you use as the primary search query in such case. Instead of such WHERE filtering, it would seem wiser to have all the user's textual inputs bundled in the SOSL's FIND search term, especially having the following performance consideration from the Force.com SOQL and SOSL Reference in mind:
- SOSL can tokenize multiple terms within a field (for example, multiple words separated by spaces) and builds a search index off this. If you’re searching for a specific distinct term that you know exists within a field, you might find SOSL is faster than SOQL for these searches. For example, you might use SOSL if you were searching for “John” against fields that contained values like “Paul and John Company”.
So you might find it that the latter could be more efficient than the former:
FIND {textInput1* OR textInput2*} RETURNING YourTargetObject
But to give you a definite answer whether SOQL or SOSL would be more efficient is kind of an ungrateful task, as important details are missing (are the searchable fields only searchable by text, how many fields can the search check against, how complex can the custom search logic become etc). Your best bet would be to pull in some typicall searches users might make and compare both SOQL and SOSL performance.
