The latest update on Magento 2.3 has unwittingly revealed a sizeable performance problem, due predominantly to a cache query concerning frequent search terms. The key cause of this predicament is the "num_results > 0" criteria in this troubling query, which prolongs query runtime considerably, adversely affecting database efficiency. In response, it's proposed that the 'getNotCacheableResult' section of the Magento_CatalogSearch/Controller/Result/Index should be utilized exclusively, as this could markedly enhance performance. Additionally, removing the DISTINCT operator from the Query Collection might also improve performance significantly because of the existing unique constraint on query_text and store_id in the search_query table. Nonetheless, these are just temporary solutions and don’t address the root issue – the high cardinality of search terms. Therefore, as Magento progresses, it must confront these performance challenges to maintain its status as a leading e-commerce platform.
Full article here: https://medium.com/@lawsuithelpdesk/title-unraveling-the-magento-2-3-update-the-hidden-impact-on-your-database-performance-d4fd38b39d01