The Magento 2.3 upgrade, praised for its features, has unexpectedly hindered database performance. The "Popular Search Term Cache" feature uses an inefficient SQL query that increases database CPU load, slowing down the system. This inefficiency stems from the fact that the query speed dwindles as the search_query table size increases. Most significant is the 'num_results > 0' condition in the query, which exacerbates this load. Despite removing this condition, the query remains slow due to the table's large size and lack of indexing. The unnecessary use of DISTINCT operator further contributes to the load. Removing it improves performance but could affect the search feature's functionality. Therefore, the Magento 2.3 upgrade inadvertently affects platform performance.
Full article here: https://medium.com/@lawsuithelpdesk/unmasking-the-hidden-cpu-load-culprit-the-overlooked-magento-2-3-f9e1276e84cc