Skip to content
Lawsuit Help Desk

Lawsuit News Center

Revolutionizing E-Commerce: Unmasking the Search Performance Pitfall in Magento and How to Outsmart it

Revolutionizing E-Commerce: Unmasking the Search Performance Pitfall in Magento and How to Outsmart it

Revolutionizing E-Commerce: Unmasking the Search Performance Pitfall in Magento and How to Outsmart it

In an e-commerce landscape dominated by platforms like Magento, an unanticipated hiccup can make or break the user experience. Unfortunately, a persistent flaw impacting search performance, linked to a common query associated with the "Popular Search Term Cache" feature, has been plaguing various Magento versions, including the 2.4.6 Commerce version. Our deep dive uncovers this flaw – the redundant DISTINCT operator – and presents savvy solutions to outmaneuver this pitfall, revolutionizing e-commerce performance for businesses utilizing Magento.

Unraveling the Culprit: A Deep Dive into the Magento Search Performance Issue

The heart of the matter lies in an unsuspecting feature of Magento: the Popular Search Term Cache, introduced in the Magento 2.3.0 update. While this feature was undoubtedly designed to enhance user experience, it has inadvertently resulted in a significant performance issue. This performance drawback is triggered by a frequent query associated with the feature that is rather taxing on the database CPU load. The query reads: "SELECT DISTINCT COUNT(*) FROM search_query AS main_table WHERE (main_table.store_id = 1) AND (num_results > 0)". The larger the search_query table, the longer this query takes to complete, slowing down search operations and causing a strain on the database, especially when the search_query table swells to millions of search terms.

Behind the Scenes: Unveiling the Redundancies Impacting Search Performance in Magento

In the heart of Magento’s search performance issue lies the notorious DISTINCT operator. Used in the query "SELECT DISTINCT COUNT(*) FROM search_query AS main_table WHERE (main_table.store_id = 1) AND (num_results > 0)," the DISTINCT operator's presence has been found to be redundant and detrimental to search performance.

Navigating the Maze: Decoding the Impact of the DISTINCT operator on Search Performance

Closer analysis of the problematic query reveals that the DISTINCT operator used within it is a significant performance detractor. The query employs the DISTINCT operator to return a unique count of search terms, but this results in a needlessly complex and taxing operation.

Transforming the Landscape: Innovating Solutions to Improve Search Performance

Addressing the search performance issue requires a two-fold approach. The first step is to remove the DISTINCT operator from the query. This modification can be made in \Magento\Search\Model\ResourceModel\Query\Collection, resulting in significant performance improvement.

Bypassing the Bottleneck: Exploring Potential Solutions to the Performance Dilemma

Several potential solutions present themselves to bypass this performance bottleneck. One such workaround involves overriding the execute function in the Magento_CatalogSearch/Controller/Result/Index to remove the getCacheableResult code. This effectively reverts Magento's behavior back to how it was before the 2.3.0 update, significantly reducing the strain on the server.

Beyond the Horizon: Looking Towards a Future of Enhanced E-commerce Performance with Magento

The Magento performance dilemma is not insurmountable. The proposed solutions of removing the DISTINCT operator and managing the search_query table's size promise a future of enhanced e-commerce performance. However, this requires Magento to acknowledge the issue and take the necessary steps to address it in their updates.

Accordingly, it's clear that the introduction of the Popular Search Term Cache, and more specifically the use of the DISTINCT operator in its associated query, has unintentionally become a significant speed bump in the otherwise smooth journey of Magento-powered e-commerce. However, with a keen eye for detail and a strategic mind, we've identified potential workarounds, including:

  • Removing the getCacheableResult code to revert to pre-2.3.0 behaviour,
  • Editing the problematic query to omit the redundant DISTINCT operator,
  • Adding an index on the search_query table for faster data access,
  • Asynchronously inserting search terms in batches or limiting the number of terms tracked.

Therefore, robust e-commerce performance with Magento is still within our grasp. It's important to remember that these workarounds are interim solutions as we await Magento's acknowledgement and rectification of the problem in future updates. This pressing issue is not just about improving user experience, but also about safeguarding Magento's standing as an efficient and dependable e-commerce platform. So, here's to hoping that Magento transforms this performance pitfall into a triumph, revolutionizing e-commerce once again.