The N+1 Trap on Magento 2 Product Pages: getUsedProducts and Friends

The PDP loads slow. Not disastrously slow, just slightly-too-slow, the kind of slow that survives the first few rounds of performance work because it looks fine on a standard product page and only gets noticed when someone profiles a configurable with a lot of options.

You open the New Relic trace. The render span is 1.8 seconds and most of it is MySQL. You drill in. There are 178 individual queries on this one PDP, and 156 of them look almost identical: SELECT * FROM cataloginventory_stock_item WHERE product_id = ? with a different id each time. Forty-eight of those child ids belong to the configurable's variants. The rest are coming from somewhere else. Welcome to N+1 on a Magento 2 product page.

TL;DR

• A configurable product with 50 children fetched via getUsedProducts() will, in the default Magento rendering path plus a handful of common plugins, cost you roughly three extra queries per child: one stock lookup, one or two per-attribute lookups. That's 150+ extra queries per PDP.
• The usual ways this gets worse: getUsedProducts() being called more than once per request, plugins on Configurable::getUsedProducts that iterate the children again, and feed exports that call $productRepository->getById($id) in a loop.
• The fix has the same shape every time: collect the ids first, fetch everything in one collection with addFieldToFilter('entity_id', ['in' => $ids]), use joinAttribute for the attributes you need, and grab stock in bulk via StockItemRepository::getItems($productIds).
• N+1 is one of the easiest bugs to spot. It's also one of the most consistently shipped. If you've never looked, you have it.

What N+1 means in Magento

The pattern is older than Magento. You fetch a list of N things in one query, then you fetch one associated thing per item, one at a time, in a loop. You end up with 1 + N queries when you could have gotten away with two.

Magento has two APIs for loading data, and both can fall into this. Collections, which issue a single SQL query for a batch of rows, and repositories, which are built for "give me the one thing with this id." The repository API is convenient and reads well at the call site. Called in a loop, it's a disaster. Every $this->productRepository->getById($id) is a product load, an EAV attribute join, and a bunch of decorator overhead, and you're paying for all of it N times.

The short version: collections are your friend, and any time you see a loop over ids calling an "ById" / "bySku" / "ByCode" method, assume you're looking at a bug until proven otherwise.

The primary culprit: getUsedProducts() called more than once

Configurable products in Magento have a helper: Magento\ConfigurableProduct\Model\Product\Type\Configurable::getUsedProducts(). It loads the children as a collection and caches them on the configurable instance for the rest of the request. That part is fine. The problems start when something breaks the caching.

The most common breakage: plugins. Two or three modules each add a plugin around getUsedProducts, each one calls the original method to get the children, each one does "something" with the result, and each call lands on a fresh instance that doesn't share the cache. You end up with getUsedProducts being called three or four times per request on the same configurable, and each call re-runs the query plus a pile of per-child work.

A simple diagnostic: add an error_log inside a plugin on Magento\ConfigurableProduct\Model\Product\Type\Configurable::getUsedProducts with a debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 2). Load a PDP. Count the invocations. One per request is healthy. Anything more than one is the first thing to fix, because everything downstream multiplies on top of it.

The second layer: stock lookups per child

For every child the render path walks, Magento 2 needs to know "is this child in stock right now". The convenience API is $this->_stockRegistry->getStockItem($childId). Called in a foreach over 50 children, that's 50 individual cataloginventory_stock_item queries, one per child.

The fix is StockItemRepository::getItems($productIds), which accepts an array of product ids and returns the stock items in a single query. Same data, one round-trip instead of N.

 1// the bug
 2foreach ($configurable->getUsedProducts() as $child) {
 3    $stock = $this->stockRegistry->getStockItem($child->getId());
 4    if (!$stock->getIsInStock()) {
 5        continue;
 6    }
 7    // ...
 8}
 9 
10// the fix
11$childIds = array_map(fn($c) => (int) $c->getId(), $configurable->getUsedProducts());
12$stockItems = $this->stockItemRepository->getItems($childIds);
13$stockByProduct = [];
14foreach ($stockItems as $item) {
15    $stockByProduct[$item->getProductId()] = $item;
16}
17 
18foreach ($configurable->getUsedProducts() as $child) {
19    $stock = $stockByProduct[$child->getId()] ?? null;
20    if ($stock === null || !$stock->getIsInStock()) {
21        continue;
22    }
23    // ...
24}

Same business logic, same output, one query instead of fifty.

The third layer: attribute lookups per child

The last common N+1 in this path: $child->getResource()->getAttribute('color') called inside the same loop. Each call, if the attribute isn't already loaded on the child, goes back to eav_attribute to read the metadata and sometimes to catalog_product_entity_varchar (or whichever backend table the attribute lives in) to read the value. It's cheap on a single call. On 50 children with two attributes per child, it's 100 extra queries you didn't plan for.

The fix is to ask the collection for the attributes up front. Two shapes, depending on how you got the children:

 1// if you own the load, use addAttributeToSelect on the collection
 2$collection = $this->childCollectionFactory->create()
 3    ->addAttributeToSelect(['color', 'size', 'manufacturer'])
 4    ->addFieldToFilter('entity_id', ['in' => $childIds]);
 5 
 6// if you're reading from getUsedProducts and can't change the load,
 7// force the attributes onto the already-loaded objects in one go
 8$this->productRepository->getList(
 9    $this->searchCriteriaBuilder
10        ->addFilter('entity_id', $childIds, 'in')
11        ->create()
12);

addAttributeToSelect adds the attribute to the collection's underlying select; the children come back with the attribute pre-loaded and the in-loop getResource()->getAttribute() call is a no-op.

The feed-export variant

The same pattern, different scene. Instead of a PDP rendering a configurable, you have a feed-export cron job producing a Google Merchant Center feed with 10,000 products. The controller or cron class does roughly this:

1foreach ($productIds as $id) {
2    $product = $this->productRepository->getById($id);
3    $parentId = $this->configurableType->getParentIdsByChild($id)[0] ?? null;
4    $parent = $parentId !== null ? $this->productRepository->getById($parentId) : null;
5    // build feed row
6}

Ten thousand iterations, three productRepository->getById calls each, plus a getParentIdsByChild query. Thirty thousand individual product loads. The cron that's supposed to finish in five minutes is running for an hour. Worse, every load comes with its own EAV attribute joins, so the query count balloons into six figures.

The fix is to load the products in batches with a collection, not one at a time:

 1$batchSize = 500;
 2foreach (array_chunk($productIds, $batchSize) as $batch) {
 3    $collection = $this->productCollectionFactory->create()
 4        ->addAttributeToSelect($feedAttributes) // the 5-10 you actually use
 5        ->addFieldToFilter('entity_id', ['in' => $batch]);
 6 
 7    $stockItems = $this->stockItemRepository->getItems($batch);
 8 
 9    foreach ($collection as $product) {
10        // build feed row, read $stockItems[$product->getId()] for stock
11    }
12}

Batching is important. A single addFieldToFilter('entity_id', ['in' => $tenThousandIds]) generates a SQL IN (...) clause that most MySQL servers accept but some will choke on. 500 at a time is the boring, safe size.

The fix: one query per N things, not N queries per thing

The template for every N+1 fix in Magento has the same three moves.

1. Collect the ids you need before the loop runs. Usually you already have them on the parent object or the initial query result.
2. Batch-fetch everything in one or two queries using a collection or a bulk-capable repository method. Pre-index the result by id into a plain PHP array.
3. Run the loop over the original list, looking up the pre-fetched data from the array instead of calling the per-item API.

Almost every bulk performance fix in Magento 2 reduces to this shape. Feed exports, report builders, PDP render paths, and basically any hot code path that walks a list of children with per-child lookups. The fix is always "move the lookup out of the loop."

Before and after, with receipts

Back to our 50-child configurable.

Before: 1 query to load the configurable, 1 for getUsedProducts (times however often the plugins re-call it, call it three), 50 stock queries, 100 attribute queries (two per child), plus another 25 queries for things like URL rewrites, category membership, image galleries. Call it 180 queries and ~1.5 seconds of PDP TTFB, most of it MySQL round-trips.

After: 1 query for the configurable, 1 for the children collection with the needed attributes joined, 1 for bulk stock via getItems, plus the remaining overhead queries. Call it 30 queries and under 400ms of MySQL time.

The numbers aren't surgical. You'll land somewhere in a range depending on the store's module load, the attribute set size, and the Marketplace extensions that have their own plugins on the configurable type. The shape is reliable though. Fixing the three N+1 layers on a busy PDP with a lot of variants moves the TTFB by roughly a second, and that's visible to real customers.

Related reading

• Diagnosing a Sub-30% Varnish Hit Rate in Magento 2. Most stores find out about N+1 because the PDP feels slow. The first thing anyone checks is the cache side, and the cache side often has its own independent problems. Run that audit first and you'll know whether you're looking at a cache miss or a real PHP bottleneck.

The N+1 detection checklist

1. Baseline query count on a known-bad path

   New Relic, Tideways, or bin/magento dev:profiler:enable then watch the request. A PDP with 40+ children that issues more than 50 SQL queries is almost certainly N+1-y. Record the number.

2. Grep for getById and getBySku in loops

   grep -rn --include='*.php' -E '(productRepository|categoryRepository|stockRegistry)->\w+ById' app/code/. Any match inside a foreach or for block is a candidate. Read it, confirm.

3. Audit every plugin on Configurable::getUsedProducts

   grep -rn --include='*.xml' 'getUsedProducts' app/code/ vendor/. More than one plugin from more than one vendor is a strong signal the method is being called multiple times per request, multiplying everything downstream.

4. Move per-child stock lookups to getItems

   Replace every foreach with $stockRegistry->getStockItem($id) inside by a single StockItemRepository::getItems($ids) call ahead of the loop, with the result pre-indexed by product id.

5. Move per-child attribute lookups to the collection load

   addAttributeToSelect on the collection that produced the children. If the children come from getUsedProducts and you can't touch the load, re-hydrate with a searchCriteria call instead of per-child resource reads.

6. Batch feed exports and reports

   Never loop getById over more than a handful of ids. array_chunk into batches of 500, load each batch as a collection with only the attributes the feed needs, pre-fetch bulk stock per batch.

7. Re-profile after each fix

   Same trace, same path. Query count should drop by a factor of five to ten on the PDP path, a factor of a hundred or more on feed-export paths. TTFB should drop by roughly whatever the MySQL time was.

8. Add a CI guard

   A static-analysis rule or a simple test that dumps the query log for one PDP request and fails if the count exceeds a reasonable threshold (40 for a configurable PDP is generous). Cheap insurance against the next plugin that reintroduces the bug.