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 turns a single index-backed query into one COUNT query per anchor category (a classic N+1), and the only symptom is latency that grows with your category count.

Why Magento 2 runs slow here

The class MagentoCatalogModelResourceModelCategoryCollection 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(), which fires a recursive COUNT(DISTINCT product_id) against catalog_category_product joined to catalog_category_entity.

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 correct-but-slow fallback: getProductsCountFromCategoryTable() returns the right number, so counts stay accurate — the regression reads as "just slow," not "broken."
• No query-count assertion around category collection loads, so the extra per-category queries go unnoticed until the tree feels heavy at scale.

1. Confirm the typo in your core file

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

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

For a precise check on just the buggy guard (recommended):

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, your version is already patched and you can stop.

2. Measure the N+1 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, each with its own entity_id/path LIKE bind. That scaling-with-N 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. In loadProductCount(), change the guard to reference the variable that actually holds the bulk result:

// In MagentoCatalogModelResourceModelCategoryCollection::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 getProductsCountFromCategoryTable() only runs for categories genuinely absent from the index.

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 categories truly missing from catalog_category_product_index. A drop from N per-category COUNTs to roughly nothing confirms the N+1 is gone and the index-backed bulk path is doing the work.

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 correct-but-expensive results, masking correctness while degrading performance.
• Core patches without an accompanying test, which can silently regress on the next Magento upgrade if upstream reorganizes the method.

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 the right numbers — the 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, 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.