Fixing Slow Magento 2 Category Pages: A One-Character N+1 Typo (Composer Patch Fix)

One of the quieter Magento 2 performance regressions hides in plain sight: your admin category tree and anchor category listings get sluggish, but nothing shows up in the logs. No exception, no warning, no var/log/exception.log entry. The culprit is a single-character typo in core — isset($categoriesProductsCount[$item->getId()]) — where the guard variable has an extra s compared to the variable that actually holds the bulk count result. That mismatch throws away a pre-fetched, index-backed bulk count and instead fires a recursive COUNT query per anchor category that has children (a 1+N pattern), and the only symptom is latency that grows with your catalog.

This bug shipped in the stock Magento 2.4.x line through 2.4.6 and was fixed upstream in 2.4.7, which uses the corrected singular variable. If you are on 2.4.7 or later, you do not have this bug — do not patch. The fix below is for stores still on 2.4.6 or earlier. The grep in Step 1 confirms which side of that line your install sits on before you change anything.

Why Magento 2 runs slow here

The class Magento\Catalog\Model\ResourceModel\Category\Collection pre-fetches anchor product counts once via fetchPairs() into $categoryProductsCount (singular), reading from the index table catalog_category_product_index. The loop over anchor categories then checks isset($categoriesProductsCount[$item->getId()]) (plural) before using that bulk result. Because $categoriesProductsCount was never defined, the isset() is always false. Every anchor category falls through to the ternary's else branch, getProductsCountFromCategoryTable(). That method only issues a query when the category has descendants — it is gated by if ($item->getAllChildren()) — and when it runs it fires a recursive COUNT(DISTINCT product_id) against catalog_category_product joined to catalog_category_entity.

So the real shape is one wasted bulk query that always runs (its result is silently discarded), plus one recursive COUNT per anchor category that has child categories. Leaf or childless anchor categories short-circuit to 0 with no query, so the per-category query count you observe will be at or below your anchor count, not exactly equal to it.

Common ways this slips through:

• Variable-name drift: a refactor split the bulk fetch from the per-item lookup, and the guard's name diverged from the fetched variable by one letter.
• PHP's lenient isset(): referencing an undefined variable inside isset() raises no warning and no TypeError, so the bug never reaches your logs.
• A close-enough fallback: getProductsCountFromCategoryTable() returns a plausible number for most categories, so the regression reads as "just slow," not "broken." (The two paths are not strictly identical — more on that below.)
• No query-count assertion around category collection loads, so the extra per-category queries go unnoticed until the tree feels heavy at scale.

One subtlety worth flagging up front: the bulk index path and the fallback path do not always return the same number. The bulk query counts catalog_category_product_index rows with a visibility filter (cat_index.visibility IN getVisibleInSiteIds()); the fallback counts DISTINCT product_id in the raw catalog_category_product table via a recursive path LIKE, with no visibility predicate. After patching, some category counts may legitimately shift to the visibility-filtered value. That is the index-backed path being correct, not a new bug.

1. Confirm the typo in your core file

Before patching anything, prove the mismatch exists in the version you're running.

grep -nE 'categoriesProductsCount|categoryProductsCount' \
  vendor/magento/module-catalog/Model/ResourceModel/Category/Collection.php

grep -E is required here so the | is treated as alternation, not a literal pipe. For a precise check on just the buggy guard (recommended), single-quote the pattern so the shell does not expand the $:

grep -n 'isset($categoriesProductsCount' \
  vendor/magento/module-catalog/Model/ResourceModel/Category/Collection.php

You should see a hit on the plural $categoriesProductsCount inside isset() alongside the singular assignment $categoryProductsCount = $this->_conn->fetchPairs($countSelect);. If the plural form returns nothing (exit code 1, no output), your version is already patched — you are on 2.4.7+ or a back-patched build — and you can stop here.

2. Measure the wasted COUNTs before you touch anything

Don't fix on faith — capture the query pattern first. Enable the general log briefly, load the admin Catalog > Categories tree, then turn it off.

SET GLOBAL general_log = 'ON';
-- load the admin category tree in another window, then:
SET GLOBAL general_log = 'OFF';
SELECT argument FROM mysql.general_log
WHERE argument LIKE '%COUNT(DISTINCT%product_id%'
  AND argument LIKE '%catalog_category_product%';

You should see one COUNT(DISTINCT main_table.product_id) per anchor category that has children, each with its own entity_id/path LIKE bind. Anchor categories with no descendants emit no COUNT at all, so expect a row count at or below your anchor count — that scaling-with-catalog pattern is the regression. Turn general_log off immediately; it is heavy and not something to leave running.

3. Apply the one-character fix as a composer patch

Never hand-edit vendor. Ship this through cweagans/composer-patches against magento/module-catalog so it survives composer install. The edited lines live in the public loadProductCount() (which the protected _loadProductCount() simply delegates to). Change the guard to reference the variable that actually holds the bulk result:

// In Magento\Catalog\Model\ResourceModel\Category\Collection::loadProductCount():
- $productsCount = isset($categoriesProductsCount[$item->getId()])
-     ? (int)$categoriesProductsCount[$item->getId()]
+ $productsCount = isset($categoryProductsCount[$item->getId()])
+     ? (int)$categoryProductsCount[$item->getId()]
      : $this->getProductsCountFromCategoryTable($item, $websiteId);

After this, the isset() resolves true for any anchor category present in the bulk result, and the bulk, visibility-filtered count is used. Note that an anchor whose true count is 0 is absent from fetchPairs() (so isset() is still false) and continues to hit the fallback even after the patch — "missing from the result set" is not the same as "missing from the index." That is expected, and it is a handful of cheap calls rather than the full per-category storm.

4. Re-apply patches and verify the bulk path is active

composer install 2>&1 | grep -i 'category-count'
grep -n 'isset($categoryProductsCount' \
  vendor/magento/module-catalog/Model/ResourceModel/Category/Collection.php
php bin/magento cache:flush

You should see the patch apply cleanly and the grep return the singular $categoryProductsCount. If the patch fails with "Could not apply patch," your line context drifted — regenerate it against your exact module version.

5. Re-measure: one bulk query, no per-category COUNTs

Reload the category tree and recheck the log.

SELECT COUNT(*) FROM mysql.general_log
WHERE argument LIKE '%COUNT(DISTINCT%product_id%'
  AND argument LIKE '%catalog_category_product%';

You want zero — or a small number only for anchor categories with a true count of 0 that fall through to the fallback even post-patch. A drop from N per-category COUNTs to roughly nothing confirms the wasted bulk query is finally being read and the index-backed path is doing the work. Spot-check a few category counts in the admin: some may move to the visibility-filtered value, which is the corrected behavior.

When this points to a deeper problem

A typo behind an isset() is the visible failure, but the conditions that let it ship are systemic:

• No automated query-count or N+1 detector around hot collection loads, so a silent per-item query path ships undetected.
• Reliance on PHP's permissive isset() over undefined variables — PHPStan or Psalm at a stricter level would flag the undefined name at CI time.
• Defensive fallbacks that return close-but-different results, masking the regression while quietly changing some numbers.
• Core patches without an accompanying test, which can silently regress on the next Magento upgrade if upstream reorganizes the method. Since 2.4.7 already carries the upstream fix, this patch should be removed (not carried forward) when you upgrade past 2.4.6.

We've seen this most often when a store's catalog grows past a few hundred anchor categories and the admin tree slowly becomes unusable while every page still renders plausible numbers — the apparent accuracy hides the cost. Audit every resource-model loop that pairs a bulk fetchPairs()/fetchAll() with a per-item isset() guard, and confirm the guard names the exact variable the bulk result was assigned to. Patching the typo buys you back the latency today; an N+1 assertion and stricter static analysis keep it from creeping back tomorrow.

You now have everything you need to confirm this on your own store, scope it to the versions that actually carry the bug, ship the fix as a durable composer patch, and prove the per-category queries are gone. It's a one-character change with an outsized payoff.