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.