fbpx
Unmasking the Hidden Performance Menace in Magento 2.3: An In-Depth Analysis and Solution for the Distinct Count Search Query Dilemma

Unmasking the Hidden Performance Menace in Magento 2.3: An In-Depth Analysis and Solution for the Distinct Count Search Query Dilemma

E-commerce operations can be disrupted by seemingly minor issues, such as a SELECT DISTINCT COUNT(*) query related to cache management of popular search terms in Magento 2.3. This query is problematic due to the DISTINCT operator, used to eliminate duplicates from results. However, it produces undue CPU stress because of the existing uniqueness constraint on query_text and store_id in the search_query table. The issue creates high CPU usage, sluggish keyword search performance, and an inefficient admin panel, persisting in versions post 2.3.4. The solution involves removing the DISTINCT operator from the query and addressing high search term cardinality through tactics like asynchronous insertion, reducing search terms, or adding an index to the search_query table.

Full article here: https://lawsuithelpdesk.com/unmasking-the-hidden-performance-menace-in-magento-2-3-an-in-depth-analysis-and-solution-for-the-distinct-count-search-query-dilemma/

YouTube
LinkedIn
LinkedIn
Share