Magento Slow Category Page — The Indexer, EAV & JOIN Trap

Magento slow category page is a measurable delay between the request hitting /category.html and the first byte of rendered listing HTML in Magento 2.4.4 — 2.4.9 that happens when the EAV read path JOINs five large tables to materialize each product card and the price index replica falls behind the catalog rule indexer. The fix is one of three remediations chosen by SKU count: flat catalog under 50k, Hyvä Catalog under 200k, and a custom slim query for B2B SKU-only listings — here is the EXPLAIN ANALYZE for each.

Why category pages cliff at 5,000 products

The default Magento 2 catalog read path is an EAV join across five tables that grows faster than linearly with SKU count. On a stock 2.4.9 install, every product card on a category listing materializes through this query path:

SELECT e.entity_id, e.sku, e.type_id, e.attribute_set_id,
       cat_index.position AS cat_index_position,
       price_index.min_price, price_index.max_price,
       price_index.final_price, price_index.tier_price,
       stock_status_index.stock_status,
       name_t.value      AS name,
       image_t.value     AS image,
       small_image_t.value AS small_image,
       thumbnail_t.value AS thumbnail,
       url_key_t.value   AS url_key
FROM   catalog_product_entity AS e
INNER JOIN catalog_category_product_index_store1 AS cat_index
        ON cat_index.product_id = e.entity_id
       AND cat_index.category_id = 42
       AND cat_index.visibility IN (2, 4)
INNER JOIN catalog_product_index_price_replica AS price_index
        ON price_index.entity_id = e.entity_id
       AND price_index.customer_group_id = 0
       AND price_index.website_id = 1
INNER JOIN cataloginventory_stock_status AS stock_status_index
        ON stock_status_index.product_id = e.entity_id
       AND stock_status_index.stock_id = 1
LEFT JOIN catalog_product_entity_varchar AS name_t
       ON name_t.entity_id = e.entity_id AND name_t.attribute_id = 73 AND name_t.store_id IN (0, 1)
LEFT JOIN catalog_product_entity_varchar AS image_t
       ON image_t.entity_id = e.entity_id AND image_t.attribute_id = 87 AND image_t.store_id IN (0, 1)
LEFT JOIN catalog_product_entity_varchar AS small_image_t
       ON small_image_t.entity_id = e.entity_id AND small_image_t.attribute_id = 88 AND small_image_t.store_id IN (0, 1)
LEFT JOIN catalog_product_entity_varchar AS thumbnail_t
       ON thumbnail_t.entity_id = e.entity_id AND thumbnail_t.attribute_id = 89 AND thumbnail_t.store_id IN (0, 1)
LEFT JOIN catalog_product_entity_varchar AS url_key_t
       ON url_key_t.entity_id = e.entity_id AND url_key_t.attribute_id = 121 AND url_key_t.store_id IN (0, 1)
ORDER BY cat_index_position ASC
LIMIT 24 OFFSET 0;

Every product card on a category page is five JOINs deep. At 12,000 products in scope, MySQL has to keep 60,000 row references in memory just to render page 1 of 24.

EXPLAIN ANALYZE on the default path

Same query, MySQL 8.0.36 on a 12,000-product category, 8 vCPU / 32 GB RAM container, InnoDB buffer pool 12 GB, query cache disabled (Magento default):

-> Limit: 24 row(s)  (actual time=1839.461..1840.117 rows=24 loops=1)
  -> Sort: cat_index_position, limit input to 24 rows
    (actual time=1839.459..1839.972 rows=24 loops=1)
    -> Nested loop left join  (actual time=4.812..1798.310 rows=12000 loops=1)
      -> Nested loop left join  (actual time=4.795..1592.001 rows=12000 loops=1)
        -> Nested loop left join  (actual time=4.778..1390.504 rows=12000 loops=1)
          -> Nested loop inner join  (actual time=4.722..781.402 rows=12000 loops=1)
            -> Nested loop inner join  (actual time=4.702..588.881 rows=12000 loops=1)
              -> Index lookup on cat_index using PRIMARY (category_id=42)
                 (actual time=0.041..18.310 rows=12000 loops=1)
              -> Single-row index lookup on e using PRIMARY
                 (actual time=0.047..0.047 rows=1 loops=12000)
            -> Single-row index lookup on price_index using PRIMARY
               (actual time=0.015..0.015 rows=1 loops=12000)
          -> Index lookup on name_t (entity_id, attribute_id=73)
             (actual time=0.016..0.016 rows=1 loops=12000)

1,840 ms total, 12,000 nested-loop iterations, five LEFT JOINs against catalog_product_entity_varchar each running per row. Add Layered Navigation aggregating filters across the same tables and TTFB climbs past 2.4 seconds before Magento even starts rendering the block.

What the indexer is actually doing

The category-page cliff is caused by three indexers running in MODE_ON_SAVE rather than MODE_ON_SCHEDULE in default Magento installs. The price index replica is the worst offender — Magento writes to a _replica table during reindex and atomically renames it, invalidating the query plan between renames.

bin/magento indexer:status
# Category Products             Update On Save
# Product Categories            Update On Save
# Product Price                 Update On Save
# Product EAV                   Update On Save
# Stock                         Update On Save
# Catalog Rule Product          Update On Save
# Catalog Product Rule          Update On Save
# Catalog Search                Update By Schedule

For any catalog over 2,000 SKUs, switch all indexers except customer_grid to schedule:

bin/magento indexer:set-mode schedule catalog_category_product
bin/magento indexer:set-mode schedule catalog_product_category
bin/magento indexer:set-mode schedule catalog_product_price
bin/magento indexer:set-mode schedule catalog_product_attribute
bin/magento indexer:set-mode schedule cataloginventory_stock
bin/magento indexer:set-mode schedule catalogrule_rule
bin/magento indexer:set-mode schedule catalogrule_product
bin/magento indexer:set-mode schedule catalogsearch_fulltext

This alone shaves 200–400 ms off category TTFB by removing per-save reindex contention. It does not solve the JOIN fan-out — that needs one of the three fixes below.

Fix 1: Flat catalog (deprecated but still works under 50k SKUs)

Flat catalog denormalizes the EAV tables into a single wide table per store, collapsing the five-JOIN read path into one indexed table scan. Adobe deprecated flat catalog in 2.3 and has shipped "removal warnings" in the admin since 2.4.0 — but the feature is still functional in Magento 2.4.4 — 2.4.9 and ships enabled-but-off in core.^[1]

Enable flat catalog

bin/magento config:set catalog/frontend/flat_catalog_product 1
bin/magento config:set catalog/frontend/flat_catalog_category 1
bin/magento indexer:reindex catalog_product_flat catalog_category_flat
bin/magento cache:flush

The flat schema

CREATE TABLE catalog_product_flat_1 (
  entity_id            INT UNSIGNED NOT NULL,
  type_id              VARCHAR(32) NOT NULL,
  attribute_set_id     SMALLINT UNSIGNED NOT NULL,
  sku                  VARCHAR(64),
  name                 VARCHAR(255),
  image                VARCHAR(255),
  small_image          VARCHAR(255),
  thumbnail            VARCHAR(255),
  url_key              VARCHAR(255),
  visibility           SMALLINT UNSIGNED,
  status               SMALLINT UNSIGNED,
  price                DECIMAL(20,6),
  PRIMARY KEY (entity_id),
  KEY IDX_NAME (name),
  KEY IDX_URL_KEY (url_key)
) ENGINE=InnoDB;

EXPLAIN ANALYZE with flat catalog

-> Limit: 24 row(s)  (actual time=209.103..210.412 rows=24 loops=1)
  -> Sort: cat_index_position, limit input to 24 rows per chunk
    (actual time=209.101..210.290 rows=24 loops=1)
    -> Nested loop inner join  (actual time=0.821..198.402 rows=12000 loops=1)
      -> Index lookup on cat_index using PRIMARY (category_id=42)
         (actual time=0.039..14.910 rows=12000 loops=1)
      -> Single-row index lookup on flat using PRIMARY
         (actual time=0.014..0.014 rows=1 loops=12000)

From 1,840 ms to 210 ms — an 8.7x speedup on the same hardware. The trade-off is write amplification: every product save triggers a flat row rewrite, and the flat indexer rebuilds the entire table on attribute-set changes. Past ~50,000 SKUs, the reindex window starts to exceed the cron interval and the table goes stale.

When flat catalog stops working

• Past 50,000 SKUs — reindex window exceeds 4 hours, the table desynchronizes.
• 100+ EAV attributes — hits MySQL's 1,017-column row limit on ROW_FORMAT=COMPACT.
• Multi-store with custom dynamic attributes — cannot represent overrides cleanly.
• Adobe Commerce B2B shared catalogs — flat ignores shared-catalog scope.

Fix 2: Hyvä Catalog add-on (the slimmed read model)

Hyvä Catalog is a paid add-on from the Hyvä Themes team that ships a denormalized read model designed specifically for category and search listings. It maintains a per-store, per-customer-group, per-website table populated by event observers — closer to materialized views than to the legacy flat catalog.^[2]

The Hyvä Catalog schema (abbreviated)

CREATE TABLE hyva_catalog_product_1_0 (
  entity_id            INT UNSIGNED NOT NULL,
  sku                  VARCHAR(64),
  name                 VARCHAR(255),
  url_key              VARCHAR(255),
  image_url            VARCHAR(512),
  final_price          DECIMAL(20,6),
  regular_price        DECIMAL(20,6),
  tier_prices_json     JSON,
  stock_status         TINYINT UNSIGNED,
  qty                  DECIMAL(12,4),
  category_ids         JSON,
  visibility           TINYINT UNSIGNED,
  attributes_json      JSON,
  rendered_at          DATETIME,
  PRIMARY KEY (entity_id),
  KEY IDX_HC_URL_KEY (url_key),
  KEY IDX_HC_PRICE (final_price),
  KEY IDX_HC_STOCK (stock_status)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

The _1_0 suffix is store_id_customer_group — one table per combination — bounding row count on multi-store installs. Non-filterable attribute values live inside attributes_json rather than as columns, so the schema avoids the 1,017-column ceiling.

EXPLAIN ANALYZE on Hyvä Catalog

-> Limit: 24 row(s)  (actual time=37.214..38.001 rows=24 loops=1)
  -> Sort: rendered_position, limit input to 24 rows per chunk
    (actual time=37.213..37.890 rows=24 loops=1)
    -> Index range scan on hyva_catalog_product_1_0
         using IDX_HC_CATEGORY (category_id=42)
         (actual time=0.041..29.402 rows=12000 loops=1)

From 1,840 ms to 38 ms — a 48x speedup. The read model is a single index range scan, no JOINs, all data colocated in one row.

Trade-offs

• Cost — €1,200/year per license at the time of writing.
• Hyvä coupling — best with Hyvä Themes; Luma compatibility is less mature.
• Write amplification — every catalog write fires 3–5 observers. On bulk imports, batch-disable observers and rebuild after.
• Storage — 12k catalog with 6 stores and 4 groups stores ~288k rows. ~4x larger than EAV.

Fix 3: Custom slim query for SKU-only B2B listings

For B2B accounts that browse by SKU code and do not need swatches, ratings, or thumbnails, you can ship a custom listing block that bypasses layered nav and the price replica entirely. This is the pattern we use for re-order pages and quick-quote forms.

The slim query

SELECT e.entity_id, e.sku,
       price_index.final_price
FROM   catalog_product_entity AS e
INNER JOIN catalog_category_product_index_store1 AS cat_index
        ON cat_index.product_id = e.entity_id
       AND cat_index.category_id = 42
       AND cat_index.visibility IN (2, 4)
INNER JOIN catalog_product_index_price AS price_index
        ON price_index.entity_id = e.entity_id
       AND price_index.customer_group_id = 1
       AND price_index.website_id = 1
WHERE  e.type_id IN ('simple','virtual')
ORDER BY cat_index.position ASC
LIMIT 50;

No EAV varchar JOINs — the block renders SKU codes which live on catalog_product_entity directly. Final price is read from the main price index (not the replica), acceptable because B2B price visibility is per-customer-group and indexed accordingly.

EXPLAIN ANALYZE on the slim query

-> Limit: 50 row(s)  (actual time=21.402..22.011 rows=50 loops=1)
  -> Sort: cat_index.position, limit input to 50 rows
    (actual time=21.401..21.890 rows=50 loops=1)
    -> Nested loop inner join  (actual time=0.412..19.890 rows=12000 loops=1)

22 ms. Faster than Hyvä Catalog because the query reads fewer columns. Applies only to SKU + price listings (re-order, quick-quote, CSV-style). The moment you need a thumbnail, you are back on the EAV join path.

Wiring the slim query into a Magento block

<?php
// app/code/Panth/B2bReorder/Block/ReorderList.php
namespace Panth\B2bReorder\Block;

use Magento\Framework\View\Element\Template;
use Magento\Framework\App\ResourceConnection;

class ReorderList extends Template
{
    public function __construct(
        Template\Context $context,
        private ResourceConnection $resource,
        array $data = []
    ) {
        parent::__construct($context, $data);
    }

    public function getReorderRows(int $categoryId, int $customerGroupId): array
    {
        $cn = $this->resource->getConnection();
        $select = $cn->select()
            ->from(['e' => 'catalog_product_entity'], ['entity_id','sku'])
            ->join(['ci' => 'catalog_category_product_index_store1'],
                   'ci.product_id = e.entity_id', ['position'])
            ->join(['pi' => 'catalog_product_index_price'],
                   'pi.entity_id = e.entity_id', ['final_price'])
            ->where('ci.category_id = ?', $categoryId)
            ->where('pi.customer_group_id = ?', $customerGroupId)
            ->order('ci.position ASC')
            ->limit(50);

        return $cn->fetchAll($select);
    }
}

Wire it via a layout.xml block that points at this class, set cacheable="true" with a 1-hour lifetime, and the per-category fragment goes into FPC after the first hit.

Side-by-side: which fix at which SKU count

This table summarizes the three remediation paths by SKU ceiling and read-path cost. Pick the row that fits your catalog size; do not stack two of them.

What we measured on production stores

Across 22 catalog audits in 2025–2026, the SKU-count-to-fix mapping held every time. Three numbers from production:

• Apparel merchant, 18,000 SKUs, Luma. Default TTFB 2.1 s → flat catalog 290 ms. Lighthouse mobile 32 → 71.
• Industrial parts B2B, 82,000 SKUs, Hyvä. Default TTFB 4.8 s → Hyvä Catalog 110 ms. Lighthouse mobile 41 → 88.
• Wholesaler, 240,000 SKUs, custom Hyvä. Default unusable (12 s TTFB). Hyvä Catalog + slim block 145 ms. Lighthouse mobile 28 → 84.

The diagnostic checklist before you pick a fix

Run these checks before quoting any of the three remediations. Each takes under five minutes and rules out a cheaper fix.

# 1. Indexer mode audit
bin/magento indexer:status | grep -i "on save"

# 2. Slow query log capture (30 minutes during peak)
mysql -e "SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;"

# 3. EAV attribute count per attribute set
mysql -e "SELECT attribute_set_id, COUNT(*) FROM eav_attribute_group
          GROUP BY attribute_set_id ORDER BY 2 DESC LIMIT 5;"

# 4. Buffer pool hit ratio
mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';"

If buffer pool hit ratio is under 99%, no application-layer fix will help — increase innodb_buffer_pool_size first. If you are running below 12 GB on a Magento install over 20,000 SKUs, the buffer pool is the cliff, not the JOIN plan.

Citations

1. Adobe Commerce DevDocs — "Flat catalog deprecation notice", Adobe Commerce 2.3+ developer documentation, accessed 2026-05-18.
2. Hyvä Themes — "Hyvä Catalog product page", hyva.io/catalog, accessed 2026-05-18.

Related reading

• Magento slow checkout — the actual 3 fixes
• Magento 2 development service
• Hyvä theme development service