Discussion on Search Performance: Elasticsearch vs MySQL and Caching Solutions

Original Slack Thread

Hi, when we are using searchAcrossEntities graphQL endpoint, it is taking lot of time to get the response if the query in the input matches with many entities. When we call this endpoint will it query elasticsearch and then mysql? If it is querying mysql is there a plan to introduce caching solution like redis? Thanks!

The bottleneck for search is almost always ElasticSearch and not MySQL. The MySQL interaction is for hydration of the entities that appear on page load which is done by a batched union query. We already have in-memory caching in place for Elastic and for repeated queries you should see them return quite quickly.

In general our logic on MySQL does not really push the boundaries of performance there as we’re dealing with a single table primary index lookup for the vast majority of our queries.

We do not have any upcoming plans to implement caching at the MySQL layer as it has not been the bottleneck at any level of scaling (companies with tens of millions of entities included). Elastic would be the first place I would look in your cluster for performance bottlenecks. If you’re not already using a managed service and are trying to support a production workload with the prerequisites single node default docker based ES node that’s an easy win in performance.

In memory cache is implemented at gms right? Is there a plan to introduce caching solution like redis to separate it from gms, as gms is taking lot of resources