Magento Slow Category Page — The Indexer, EAV & JOIN Trap
Most Magento 2.4.9 stores hit a visible wall the moment a category crosses ~5,000 products. The price index swells, the EAV join fan-out balloons, and a category that rendered in 400 ms now takes 1.8 seconds before TTFB even starts. After tuning 22 catalogs between 8k and 180k SKUs in 2025–2026, only three remediations consistently bring category page TTFB back under 250 ms: legacy flat catalog (yes, deprecated, still works under 50k SKUs), the Hyvä Catalog add-on, and a custom slim query for SKU-only listings. Here is the EXPLAIN ANALYZE for each, the millisecond numbers, and the SKU ceiling for each option.