We're trying to provide a search interface in lightning community, where user can search Accounts and Contacts. No of Accounts and Contacts are in range of 2-4 millions in our Org.
Currently, what we know, total number of records returned by SOQL queries in single transaction is 50000 records. This is putting serious limitation. Now, one may ask- why there is a need to return more than 50,000 records in a single search.
Use case is : Suppose user wants to search all contacts working in 'XYZ' City. This could be more than 50, 000 records. We don't necessarily need to return 50,000(or more) in one transaction but we want search to Happen in all applicable records(Say 90,000) so that, we can provide faceted search.
For e.g, we want to provide, along with searched records(Contacts)-top 10 Accounts (with aggregated count of applicable Contacts records) as filter option in left side bar as shown in example. if Salesforce returns only 50,000 records, then facets won't work properly and might show in-correct data.
My questions, what is community's view about Salesforce search capability:
Is it suitable to build enterprise level search solution using SOQL/SOSL?
It is good enough to provide options like fuzzy logic, faceted search ?
Is there any other good solution(preferably public facing) someone implemented with any 3rd party vendors?