Skip to content
Lawsuit Help Desk

Lawsuit News Center

Unmasking Magento 2.3: Decoding the Database Debacle and How to Turbocharge Its Performance

Unmasking Magento 2.3: Decoding the Database Debacle and How to Turbocharge Its Performance

Unmasking Magento 2.3: Decoding the Database Debacle and How to Turbocharge Its Performance

In the digital commerce world, the shift to Magento 2.3 has unexpectedly catalyzed a performance crisis, stalling the database engine due to a problematic query embedded in the software's design. Rooted within the search_query table, this query, tied to the Popular Search Term Cache feature, disproportionally burdens databases with larger tables, leading to agonisingly delayed responses. This blog seeks to unravel this convoluted issue, shedding light on the ineffective fixes, pinpointing the inherent flaws within the query, and presenting potent solutions to supercharge your Magento 2.3 performance.

Unraveling the Magento 2.3 Update: The Hidden Culprit Behind Your Database Woes

The advent of Magento 2.3, although initially embraced as a beacon of technological progress, has been identified as a primary instigator of a sudden surge in database CPU load. The heart of the issue lies within the search_query table, specifically in a query introduced with this update, which is responsible for popular search term caching. As the search_query table grows in response to the volume of search terms, the query's execution time also increases proportionally. This relationship lays the groundwork for potentially crippling performance issues, particularly for those databases that house a substantial search_query table. Despite the advantages of caching popular search terms, it is evident that the introduction of this feature has inadvertently compromised the overall performance of Magento 2.3.

Dissecting the Query: How a Single Line of Code Can Bring Your Database to its Knees

Let's delve into the crux of the matter – the query. The problematic line of code is as follows: SELECT DISTINCT COUNT(*) FROM search_query AS main_table WHERE (main_table.store_id = 1) AND (num_results > 0). It may appear innocuous at first glance, yet this single query has proven to be distressingly burdensome for databases.

The primary bottleneck of this query lies within the condition (num_results > 0). In removing this part, the query's duration dramatically decreases, an insight that underscores the detrimental role of this condition. Furthermore, the DISTINCT operator in the query, which is designed to prevent duplicate entries, has been identified as an unnecessary addition. The search_query table already has a unique constraint on query_text and store_id, rendering the DISTINCT operator redundant. The misuse of this operator is a contributing factor to the query's sluggish performance.

The Illusion of Fixes: Why Conventional Remedies Fail to Tackle the Magento 2.3 Performance Issue

Various attempts to mitigate this performance issue have been documented, none of which have resulted in a reliable, long-term resolution. The fix provided in c90edaa was a well-intentioned effort, yet it has consistently failed to rectify the performance issue, even after recurring testing on the latest 2.4-develop branch.

Strategies such as disabling search suggestions or enabling Elasticsearch have also been explored, albeit with little success. These 'quick fixes' provide a temporary band-aid, diverting attention away from the root of the problem – the query. The issue has been reproduced in various Magento versions, including the Magento 2.3.4 community version and Magento 2.4.5-p4, which amplifies the urgency of finding a sustainable solution for this database debacle. As we have deciphered, the pain points of Magento 2.3's performance issues are intricately linked to a single query and its problematic use of the DISTINCT operator and the condition (num_results > 0). Recognising this is the first step towards developing effective and lasting solutions.

The Power of Optimizing Your Search: How Proper Indexing Can Turbocharge Your Magento Performance

The complexity of the issue at hand may lead some to believe that the only feasible solution is a complete overhaul of the Magento 2.3 system, but that’s not the case. A more focused and less disruptive solution lies in indexing the search_query table. As the search_query table grows, it becomes increasingly taxing for the database to scan through numerous rows just to fetch a single result. The time consumed in this process is directly proportional to your database's performance lag. By adding an index, the database can instantly locate search terms without having to trawl through the entire table. This is akin to using a well-structured index at the back of a book – a simple yet effective solution to steer clear of performance woes. However, it's noteworthy that while adding an index may resolve the performance issue, it doesn't tackle the root cause. The problem lies within the way Magento 2.3 handles search queries, specifically the unnecessary use of the DISTINCT operator. This brings us to our next point.

The Problem with DISTINCT: How One Operator Can Spell Disaster for Your Performance

The query used by Magento 2.3 includes an unwarranted DISTINCT operator, which is responsible for filtering out duplicate values from the result set. However, the search_query table already has a unique constraint on query_text and store_id. This makes the DISTINCT operator redundant, and it's this redundancy that is affecting performance negatively. The use of the DISTINCT operator forces the database to traverse through each row to ensure uniqueness, leading to a significant increase in CPU load and execution time, especially when dealing with larger tables. Removing the DISTINCT operator from the query drastically enhances performance, as has been observed in numerous instances. This realization prompts us to reconsider the nature of the fixes that have been proposed so far. In essence, the problem isn't just about the mechanics of the query but the logic behind it.

Beyond the Quick Fixes: Sustainable Solutions for Your Magento 2.3 Database Conundrum

Despite multiple attempts to update and patch Magento 2.3, the performance issue persists. The key takeaway is that the issue needs to be addressed at the query level. Quick fixes, such as disabling search suggestions or enabling Elasticsearch, fail to address the fundamental flaw. Instead, they merely provide a temporary respite from the performance lag.

The solution lies in reassessing the logic of the query. If the DISTINCT operator is removed and proper indexing is implemented, the query becomes more efficient. Additionally, asynchronous insertion of search terms in batches can prevent performance issues, as can inserting just a fraction of search terms or even disabling search term tracking altogether.

While it's important to acknowledge the benefits of the Popular Search Term Cache feature, it's equally necessary to recognize when it's causing more harm than good. Industry experts are increasingly advocating for a systemic overhaul of how Magento 2.3 handles search queries.

In conclusion, to rectify the performance issues brought by Magento 2.3, it's essential to address the glaring issues at the query level, rather than resorting to temporary fixes. Firstly, the removal of the DISTINCT operator, an unnecessary piece in the query given the unique constraints already present in the search_query table, can significantly enhance performance. Secondly, proper indexing of the search_query table can expedite the retrieval of search terms, reducing the time consumed and thereby improving the overall performance. Lastly, we need to reassess the benefits of the Popular Search Term Cache feature, balancing its functionality with the performance of Magento 2.3.

By addressing these foundational issues, we can successfully turbocharge the performance of Magento 2.3 and turn what might seem like a debacle into a triumph of technical problem-solving. A balance needs to be struck between functionality and performance, and that balance can be found by going beyond quick fixes and addressing the root of the problem. The solutions are there – we just need to implement them.