Unmasking Magento's Hidden Quirk: The Illusive Search Query Performance Dilemma That's Baffling Developers

Unmasking Magento’s Hidden Quirk: The Illusive Search Query Performance Dilemma That’s Baffling Developers

The Magento 2.3 upgrade caused unexpected CPU load hikes for many e-commerce platforms thanks to the Popular Search Term Cache feature. The major issue was a protracted query affecting platforms with millions of search terms, specifically the condition "num_results > 0", which necessitated scanning countless rows and significantly spiked database CPU use. Multiple solutions have been considered, including removing the condition or indexing the search_query table. One effective resolution entailed modifying Collection.php file to replace "$select->distinct(true)" with "$select->distinct(false)", slashing query times from 10 seconds to 2-3 seconds. Constant oversight, refinement, and optimization can help avoid similar future problems. Cooperation among developers, system administrators, and architects, and adopting efficient database optimization, query finesse, and caching methods can markedly elevate Magento platform's total search performance.

Full article here: https://medium.com/@lawsuithelpdesk/unmasking-magentos-hidden-quirk-the-illusive-search-query-performance-dilemma-that-s-baffling-3e216b2c1117