Magento Commerce platform users are battling performance issues in versions 2.3 and above due to a cache search term query problem. The issue arises as the size of the search_query table expands, straining the platform's functionality and causing high CPU load. Moreover, this problem persists in the latest 2.4-develop branch and Magento version 2.4.5-p4. The num_results > 0 command, designed to exclude zero-result queries, effects the platform's performance. Removing this command aids performance but potentially alters search result accuracy. Remedies such as ElasticSearch and disabling search suggestions have minimal effect, while removing the DISTINCT operator shows some success, risking search accuracy. A carefully optimized query, indexed search_query table, and a balanced num_results > 0 condition are envisioned solutions to address these issues without sacrificing result integrity.
Full article here: https://lawsuithelpdesk.com/unmasking-the-search-query-menace-the-disquieting-performance-issue-in-magentos-popular-search-term-cache/