Magento 2 Performance Boost: Fixing the N+1 Review Query Bottleneck

At Shopping Mover, we understand that in the fast-paced world of e-commerce, every millisecond counts. A seamless, lightning-fast user experience isn't just a luxury; it's a necessity for driving conversions and fostering customer loyalty. This is why we pay close attention to critical performance bottlenecks within platforms like Magento 2 (now Adobe Commerce and Open Source).

A significant GitHub issue, #40935, recently brought to light a severe N+1 query problem within Magento 2's review collection process. This isn't just a minor glitch; it's a core performance drain that could be silently slowing down your Magento store, particularly for products boasting a rich history of customer reviews. Let's dive deep into this critical issue and understand its implications for your e-commerce success.

The Silent Killer: Magento 2's N+1 Review Performance Culprit

The issue, meticulously reported by lbajsarowicz, details a profound performance degradation stemming from how Magento 2 handles product review rating votes. Specifically, the Magento\Review\Model\ResourceModel\Review\Collection::addRateVotes() method, which is invoked whenever product review lists are displayed (e.g., on product pages via Magento\Review\Block\Product\View\ListView) or when reviews are queried via GraphQL (ProductReviewsDataProvider / CustomerReviewsDataProvider), executes an alarming two database queries per review.

This N+1 problem manifests in two distinct, compounding ways:

1. Iterative Querying (The N+1 Core): The primary N+1 issue arises because addRateVotes() loops through each review item in the collection. For every single review, it creates and loads a separate Rating\Option\Vote collection by calling setReviewFilter($item->getId()). This results in N individual queries for N reviews, instead of a single, efficient batched query. This inefficient behavior is traceable to the Magento 2 codebase, specifically around

   https://github.com/magento/magento2/blob/2.4-develop/app/code/Magento/Review/Model/ResourceModel/Review/Collection.php#L237-L251

   .
2. Redundant fetchAll() (Doubling the Pain): Compounding the N+1, the Rating\Option\Vote\Collection::addRatingInfo() method contains a stray $connection->fetchAll($this->getSelect()); call. The result of this query is immediately discarded, meaning every individual vote collection executes its select statement twice. This effectively doubles the query count for each review, turning an already inefficient process into a performance nightmare. This inefficiency can be found at

   https://github.com/magento/magento2/blob/2.4-develop/app/code/Magento/Review/Model/ResourceModel/Rating/Option/Vote/Collection.php#L180

   .

The Real-World Impact: When N+1 Hits Your Bottom Line

The numbers speak for themselves. For a product with just 304 approved reviews, the impact is staggering:

• Queries: A whopping 609 queries executed, where only 2 were needed.
• Wall Time: An increase from 18 ms to 207 ms, representing a 91% slowdown.
• Peak Memory: A modest but still noticeable increase from 24 MB to 26 MB.

Imagine this scenario on a high-traffic product page with hundreds or even thousands of reviews. Each page load, each GraphQL API call for product reviews, is burdened by hundreds of unnecessary database operations. This translates directly to:

• Slower Page Load Times: Frustrated customers are more likely to abandon your site, impacting conversion rates and increasing bounce rates.
• Poor User Experience: Lagging interfaces diminish trust and make shopping less enjoyable.
• SEO Penalties: Search engines prioritize fast-loading websites. Slow performance can negatively affect your rankings.
• API Latency: If you're using GraphQL for headless commerce or mobile apps, this bottleneck will directly impact the responsiveness of your applications.
• Increased Server Load: More queries mean more work for your database server, potentially leading to higher hosting costs or even outages during peak traffic.

The Solution: A Batched Approach for Optimal Performance

The proposed fix, which is already in the pipeline for Magento 2.4.x, is elegantly simple and highly effective. Instead of querying for each review individually, the solution involves:

1. Loading all rating votes for all reviews in the collection with a single, optimized review_id IN (...) query.
2. Distributing these loaded votes efficiently among the respective review items.
3. Removing the redundant and discarded fetchAll() call from addRatingInfo().

Crucially, this fix ensures that the per-review getRatingVotes() value remains a Rating\Option\Vote\Collection, meaning existing templates and GraphQL consumers are unaffected. The change is entirely under the hood, delivering a massive performance boost without requiring any front-end or API adjustments.

The measured results of this batched fix are phenomenal: reducing queries from 609 to just 2, and wall time from 207 ms to 18 ms. This is a testament to Magento's ongoing commitment to performance and stability.

What This Means for Your Magento Store

If you're running a Magento 2.4.x store (Adobe Commerce or Open Source) and your products have a significant number of reviews, this issue is directly impacting your performance. Here's what you should do:

• Stay Updated: Ensure your Magento instance is always on the latest patch version. Core performance fixes like this are regularly released and are crucial for maintaining a healthy store.
• Monitor Performance: Regularly use tools like Blackfire, New Relic, or even Magento's built-in DB profiler to identify bottlenecks.
• Consider a Performance Audit: If you're on an older version or suspect other performance issues, a comprehensive audit by Magento experts can uncover hidden problems.
• Plan Your Migrations Wisely: If you're considering a migration to a newer Magento version or even to Adobe Commerce, partnering with experts like Shopping Mover ensures that performance best practices are integrated from day one.

This N+1 fix is a prime example of how seemingly small code inefficiencies can have a monumental impact on your e-commerce platform's speed and, by extension, your business's success. By addressing these core issues, Magento continues to evolve as a robust and high-performing platform.

At Shopping Mover, we specialize in Magento migrations and performance optimization. We help businesses like yours navigate complex upgrades, integrate seamlessly, and ensure your store runs at peak efficiency. Don't let hidden performance issues hold your e-commerce business back. Contact us today for a performance review or migration consultation.