The Magento 2.3 update, touted as a leap in performance, ironically led to an unexpected increase in database CPU load linked to the Popular Search Term Cache feature. An excessive query tied to the search_query table sparked a significant performance issue across different Magento versions, prompting an exploration into search performance optimization. The Magento community proposed multiple solutions, including overwriting the execute function in the Magento_CatalogSearch/Controller/Result/Index and adding an index on the search_query table. Despite these efforts, the problem persisted. The DISTINCT operator within the search_query table was seen as a major contributor to the issue, which was closed without any official resolution. The Magento 2.3 upgrade episode underlines the persistent challenge of achieving optimal performance, highlighting the necessity for ongoing innovation and cooperative problem-solving within the technology community.
Full article here: https://medium.com/@lawsuithelpdesk/decoding-the-magento-2-3-61d9eccd9692