In our org there are more than 5 Millon CaseComment record exists. User will navigate from Account record and opens a page upon a button click. User enters the keyword and it should search on the caseComment records related to the cases under that particular account.
Refer entire code base at my blog Keyword Search Series - Display paginated data using Visualforce and Datatable for CaseComments with server side search
We are seeing issues that keyword exists in casecomments but SOSL query doesn't return those records.
List<CaseComment> lstComment = [FIND :searchString IN ALL FIELDS RETURNING CaseComment (id,ParentId, CommentBody WHERE Parent.AccountId = :accountId ORDER BY CreatedDate DESC) LIMIT 1000];
Even if that Account has 10 cases and each case has max 10 case comments and we can see keyword exists in comments, but unable to show the record.
We have logged a case on premier support channel and here is the response received.
When running SOSL, you cannot be guaranteed to have consistent results. Within SOSL there are multiple conditions resulting in different limits and also this can be affected by the order of execution of SOSL. Below we will go through known conditions, limitations that could result in inconsistent results returned.
a) When you use SOSL or the Global Search features, you are using a specific search engine implemented at Salesforce.com. SOQL operates at the db level instead.
b) The way the operations work in SOSL is first perform a search across all the indexers, then filter (using the provided criteria), then apply sharing.
This could be explained in a better way if we see the order of execution of SOSL.The order of executuion is as follows:-
- The whole table is split in partitions of 50K records
- Using each partition’s SOSL index we find the Record ID for the first 10k records in each partition that match the words being searched for.
- The Record IDs are sent to the database for processing, which includes tasks like:
==> Retrieving record details
==> Sharing access check
==> Applying WHERE filters.
==> The records that are remaining are sent to the user.
If you see above,you will find that where filter is applied at the last,so it causes the inconsistency. The best way is to use distinct keywords.
Please guide how to resolve this limitation when we have large volume of CaseComments.