The Magento 2.3 upgrade has inadvertently increased database CPU load within e-commerce, mainly due to the inefficient execution of the DISTINCT COUNT query in the new Popular Search Term Cache. The DISTINCT operator’s task of counting unique rows requires scanning each row in the search_query table – a resource-draining process. Ironically, this operator seems unnecessary given the unique constraint on query_text and store_id. A remedy involves removing the DISTINCT operator, which has proved to increase query performance without compromising result quality in recent tests. Magento needs to adopt a proactive strategy for the future, including immediate corrective measures, thorough reviews of query additions, and robust pre-release testing. Adhering to this approach will uphold Magento's reputation as a dependable platform for e-commerce businesses worldwide and emphasizes the ongoing need for optimization in digital technology.
Full article here: https://lawsuithelpdesk.com/turbocharging-your-magento-2-3-an-insiders-guide-to-navigating-database-cpu-load-increases-and-optimizing-search-term-performance/