Magento's category product indexer, rewritten - up to 7.7× faster, byte-identical output
If your catalog_category_product reindex takes hours, dies with "Could not acquire lock for index", or leaves the storefront out of sync with the admin, the problem is architectural - not your hardware. This is a free, MIT-licensed drop-in replacement that fixes it.
MIT licensedMagento 2.4.6-2.4.9Adobe Commerce + Mage-OS
SimpleMage Category/Product Indexer is a free, MIT-licensed drop-in replacement for Magento 2's catalog_category_product indexer. Core resolves EAV data inside a single 13-JOIN mega-query whose IFNULL conditions defeat the MySQL optimizer; this module materialises that data once into snapshot tables and rewrites the Select objects to read from them. On a 111k-product catalog a full reindex drops from 124.4s to 16.2s (7.7× faster); on a 447k-product store a reindex that hung for over 24 hours completes in 2 minutes 41 seconds. Output is verified byte-for-byte identical to core, and it installs with one composer command - no schema changes, no configuration.
// the problem
Why core's indexer collapses on large catalogs
Magento 2's stock catalog_category_product indexer runs a single mega-INSERT...SELECT with 13 JOINs - four on catalog_product_entity_int alone, for status and visibility at both default and store-view scope. Worse, its WHERE clause wraps three EAV columns in IFNULL(store_value, default_value). That construct is not sargable: it defeats the query optimizer and forces a nested-loop join across the full Cartesian product. The bigger the catalog, the worse it scales - and the failure mode is not "slow", it is "never finishes".
Multi-hour reindex times - that often never complete at all
Million-plus row locks held simultaneously - 1 056 250 measured on a 447k-product store
"Could not acquire lock for index" - the error MySQL throws when it kills the transaction
Suspended scheduler state - indexer:status stuck on "Processing", needing a manual indexer:reset
Drift between admin and storefront - products added to a category never appear to customers
// benchmarks
Measured impact on real production catalogs
Every number below comes from a full-reindex benchmark on a copy of a real production database. In each run the index output was verified byte-for-byte identical to core Magento using full-content MD5 snapshots - every row, every column, every store view, with no sampling. Your mileage will vary with catalog size, taxonomy shape and MySQL tuning, but the architectural advantage held on every workload measured.
Magento 2.4.7-p7 - 111k products, 700+ categories
Metric
Core Magento
This module
Improvement
Wall-clock time
124.4 s
16.2 s
7.7× faster
SQL queries
6 906
2 442
2.8× fewer
Slow queries
391
35
11.2× fewer
Output vs core
-
MATCH (130 742 rows)
identical
Magento 2.4.6-p14 - 447k products, 3 store views, anchor-heavy taxonomy
Metric
Core Magento
This module
Wall-clock time
hangs (> 24 h, killed)
2 min 41 s
Row locks held simultaneously
1 056 250 (growing)
~few thousand (chunked)
Tables locked simultaneously
7 of 19 in use
4-5
JOINs in main INSERT...SELECT
13
2
EAV resolution
per-batch, via IFNULL × 8
once, materialized
Mage-OS 2.2.1 (Magento 2.4.8-p4) - 503k products, 4 store views
Metric
Core Magento
This module
Improvement
Wall-clock time
42 min 8 s
16 min 21 s
2.6× faster
Peak memory
84 MB
52 MB
1.6× less
Slow queries
48
20
2.4× fewer
Output vs core
-
MATCH (18 728 407 rows)
identical
Adobe Commerce 2.4.7-p10 - 116k products, live scheduled updates (correctness only, no timings)
Check
Result
Full reindex output vs core
MATCH (row count + per-store CRC32)
Partial (mview) reindex vs core
MATCH
Product with 3 live staging versions
exactly one snapshot row per store
// how it works
The snapshot pattern
The fix is not micro-optimising core's query - it is removing the reason that query is slow. Instead of resolving EAV attributes inside the indexing join, the module resolves them once, up front, into flat snapshot tables. Core's indexer then runs unchanged against those tables.
Acquire a lock
SnapshotBuilder::ensureFresh() takes a blocking MySQL GET_LOCK so two processes never rebuild the same snapshot concurrently. If the lock times out, the module falls back to core's own implementation rather than failing the reindex - the worst case is core's original behaviour, never a broken index.
Materialise the snapshots
Product and category EAV values are written into flat snapshot tables in chunks of roughly 5 000 rows, alongside a category ancestor map and an anchor-category snapshot. Chunking is what keeps lock counts in the low thousands instead of over a million, which is why the reindex stops tripping MySQL's limits.
Rewrite the selects
The three core action classes (full reindex, category-change rows, product-change rows) are replaced via DI preferences. Each one rebuilds core's Select objects to read from the snapshot tables instead of the EAV tables - dropping the main INSERT...SELECT from 13 JOINs to 2 and removing the IFNULL conditions that blocked the optimizer.
Hand back to core
The actual write is still core Magento: parent::execute() runs unchanged, just against faster inputs. That is why the output is byte-identical rather than merely equivalent - the module changes how the data is read, never what gets written.
// install
Install in one command
There is no configuration and no schema migration for your data. The module registers DI preferences and creates its own snapshot tables; existing reindex commands keep working exactly as before.
Core changed catalog_category_product behaviour between releases, so the module detects the installed version and mirrors its semantics - that is how output stays byte-identical across the whole supported range rather than only on the version it was written against.
🛒
Magento & Adobe Commerce
Open Source and Adobe Commerce 2.4.6-2.4.9, including Commerce staging with live scheduled updates.
🧩
Mage-OS
Mage-OS 1.x, 2.x and 3.x - version detection stays Magento-compatible, so nothing special is needed.
🐘
PHP 8.2 - 8.5
Matches the PHP window of the supported Magento releases; CI runs the full matrix.
🗄️
MySQL & MariaDB
MySQL 8.0 and MariaDB 10.4-10.11.
🏬
Multi-store & anchors
Multi-store and multi-website setups, anchor categories, configurable parent-child visibility and disabled-product handling.
⚠️
One conflict to check
Any third-party module with its own DI preference on the same three core indexer classes will conflict. Check before installing - this is the only real incompatibility.
// limitations
Known limitations, stated plainly
Two things are worth knowing before you install. Neither affects correctness on a normally operated store, but you should hear them from me rather than discover them yourself.
The bit-identical verifier is not yet automated in-repo - output is verified MD5-identical to core on real catalogs, but the runner that toggles between core and snapshot mode needs a separate process per DI configuration and lives in a companion benchmark module. The in-repo suite pins preference wiring, non-empty output and run-to-run determinism.
New store views require one full reindex - snapshot rows are materialised per existing store view, so after creating a store view run bin/magento indexer:reindex catalog_category_product once. Core requires the same step.
// why this matters
If the indexer is this broken, what else is?
A stuck indexer is rarely the only thing wrong. It is usually the most visible symptom of a store that has outgrown decisions made years earlier - EAV-heavy queries, module bloat, an upgrade deferred one release too long. I wrote this module while auditing exactly those stores. If you want the same evidence-based read on your own Magento - what is actually slow, what it costs to fix, and in what order - that is what an audit gives you.
// more
Slow store, but not sure the indexer is the cause?
Short term, run bin/magento indexer:reset catalog_category_product to clear the stuck state, then reindex during a quiet window with the indexer set to "Update by Schedule". That clears the symptom but not the cause: the error appears because core's indexing query holds over a million row locks at once until MySQL kills the transaction. The permanent fix is to stop generating that many locks, which is what this module does by chunking the work into snapshot tables.
Because core's catalog_category_product indexer runs one INSERT...SELECT with 13 JOINs and wraps three EAV columns in IFNULL(store_value, default_value). That condition is not sargable, so MySQL cannot use its indexes and falls back to a nested-loop join across a very large intermediate result. Runtime grows far faster than catalog size, which is why a store can cross a threshold and go from minutes to never finishing.
This module does not change what core writes - it changes how core reads. It replaces three action classes via DI preferences so their queries read from pre-built snapshot tables, then calls core's own execute() to do the actual write. Output has been verified byte-for-byte identical to core with full-content MD5 snapshots on real catalogs, including a run covering 18 728 407 rows. If the snapshot lock cannot be acquired, it falls back to core's implementation.
Yes. EAV joins resolve the metadata link field (row_id) and all snapshot reads are built as framework Select objects, so Commerce's staging FromRenderer applies its current-version filters normally. This was verified byte-identical on a 2.4.7-p10 Adobe Commerce catalog with live scheduled updates, including a product carrying three live staging versions.
It does not touch any core table. It adds its own snapshot tables, declared in db_schema.xml, which are dropped automatically on module:uninstall. Your catalog, category and index tables are left exactly as they are.
Run bin/magento module:disable SimpleMage_CategoryProductIndexer followed by setup:di:compile and cache:flush. The DI preferences are removed and Magento reverts to its own indexer immediately - no data migration and no cleanup, because core's tables were never modified.
Yes - MIT licensed, free for commercial use, no paid tier and no telemetry. It is on GitHub; issues and pull requests are welcome. I maintain it because I hit this problem repeatedly while auditing large Magento stores.
Want the same analysis on your own store?
The module fixes one bottleneck. An audit tells you which others are costing you - measured, prioritised and costed. Start with a free 30-minute call.