Unmasking Magento 2's Hidden Performance Drain: The N+1 Review Query Bottleneck

At Shopping Mover, we understand that every millisecond counts for e-commerce performance. A critical GitHub issue (#40935) sheds light on a significant N+1 query problem within Magento 2's review collection, impacting storefront load times and GraphQL API responsiveness. This insight delves into a core performance bottleneck that could be silently slowing down your Magento store, especially for products with numerous customer reviews.

The N+1 Performance Culprit:

The issue, reported by lbajsarowicz, details a severe performance degradation stemming from how Magento 2 handles product review rating votes. Specifically, the Magento\Review\Model\ResourceModel\Review\Collection::addRateVotes() method, invoked when displaying product review lists (e.g., Magento\Review\Block\Product\View\ListView) or querying reviews via GraphQL, executes an alarming two database queries per review.

This N+1 problem manifests in two distinct ways:

1. Iterative Querying: The primary N+1 occurs because addRateVotes() loops through each review item. For every review, it creates and loads a separate Rating\Option\Vote collection via setReviewFilter($item->getId()). This results in N individual queries for N reviews, instead of a single batched query. This behavior is traceable to lines like

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

   .
2. Redundant fetchAll(): Compounding the N+1, Rating\Option\Vote\Collection::addRatingInfo() contains a stray $connection->fetchAll($this->getSelect()); call whose result is immediately discarded. This means every individual vote collection executes its select statement twice, effectively doubling the query count for each review. This inefficiency is found at

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

   .

Impact and Proposed Solution:

The combined effect is a dramatic increase in database load. For a product with 304 approved reviews, measurements on Magento 2.4-develop revealed a staggering 609 queries, taking 207 ms. This is a critical bottleneck for high-traffic Magento stores, directly impacting page load times and server resources. The issue was initially reported as #39761 but closed as stale, highlighting its recurring nature.

The proposed fix is robust:

1. Batched Querying: Load all rating votes for the entire review collection with a single, optimized review_id IN (...) query, then distribute results.
2. Eliminating Redundancy: Remove the dead fetchAll() call from addRatingInfo().

This "batched fix" yields astounding performance gains:

• Queries: Reduced from 609 to 2 (99.7% reduction).
• Wall Time: Decreased from 207 ms to 18 ms (91% improvement).
• Peak Memory: Slightly reduced from 26 MB to 24 MB.

This fix maintains compatibility with existing templates and GraphQL consumers.

Community Takeaway:

While the provided GitHub issue thread focuses on the problem definition and proposed solution rather than extensive community discussion, the detailed analysis and significant performance improvements are crucial for any Magento 2 user. This fix, once implemented, will provide a substantial performance boost for product pages and GraphQL endpoints dealing with reviews. For merchants, this translates to faster page loads and a better user experience. For developers, it underscores the importance of optimizing collection loading mechanisms and avoiding N+1 patterns in custom code and extensions. Understanding and addressing such core bottlenecks is vital for maintaining a fast, scalable, and efficient e-commerce operation, especially during Magento migrations or when scaling an existing Adobe Commerce or Open Source instance.