PHPackages simplemage/module-category-product-indexer - PHPackages - PHPackages [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register) 1. [Directory](/) 2. / 3. [Database & ORM](/categories/database) 4. / 5. simplemage/module-category-product-indexer ActiveMagento2-module[Database & ORM](/categories/database) simplemage/module-category-product-indexer ========================================== High-performance rewrite of Magento 2's catalog\_category\_product indexer using the snapshot pattern. Solves the classic 'Could not acquire lock' hang on large catalogs (500k+ products). 2.6x-7.7x faster measured on real production databases. v1.1.0(1mo ago)2250↑41.7%8MITPHPPHP ~8.2.0 || ~8.3.0 || ~8.4.0 || ~8.5.0CI passing Since Jun 12Pushed 1mo agoCompare [ Source](https://github.com/SimpleMage/magento2-category-product-indexer)[ Packagist](https://packagist.org/packages/simplemage/module-category-product-indexer)[ Docs](https://github.com/simplemage/magento2-category-product-indexer)[ RSS](/packages/simplemage-module-category-product-indexer/feed)WikiDiscussions main Synced 1w ago READMEChangelog (2)Dependencies (14)Versions (4)Used By (0) SimpleMage - Category/Product Indexer Rewrite ============================================= [](#simplemage---categoryproduct-indexer-rewrite) [![License: MIT](https://camo.githubusercontent.com/08cef40a9105b6526ca22088bc514fbfdbc9aac1ddbf8d4e6c750e3a88a44dca/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f4c6963656e73652d4d49542d626c75652e737667)](LICENSE)[![Magento 2.4](https://camo.githubusercontent.com/4fc4be46d635599bfb958016861b2df6e5af7ae0e813d3d9e2a22eb85408141e/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f4d6167656e746f2d322e342d6f72616e67652e737667)](https://magento.com/)[![PHP 8.2-8.5](https://camo.githubusercontent.com/c159e6cb60a028086ed58082109fd4e54b7770c96e02e3796def65ca1efc7dd7/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f5048502d382e322d2d382e352d3737374242342e737667)](https://www.php.net/) **High-performance drop-in replacement for Magento 2's `catalog_category_product` indexer.** Solves the classic *"Could not acquire lock for index: catalog\_category\_product"* hang on large catalogs (500k+ products) and delivers **2.6×-7.7× faster reindex** measured on real-world production databases - without schema changes, without breaking compatibility. The problem ----------- [](#the-problem) Magento 2's stock `catalog_category_product` indexer runs a single mega-`INSERT...SELECT` with **13 JOINs**, including: - 4× `catalog_product_entity_int` (status default + store override, visibility default + store override) - 2× `catalog_category_entity_int` (is\_active default + store override) - 2× `catalog_category_product` (parent + child position) - 2× `catalog_product_entity_int` for configurable child products - `temp_catalog_category_tree_index_*` for anchor expansion - `catalog_product_relation`, `catalog_product_entity`, `catalog_product_website` Plus a `WHERE` clause with `IFNULL(store_value, default_value) = X` on three EAV columns - which **defeats the query optimizer** and forces a nested-loop join across the full Cartesian product. On any non-trivial catalog this causes: - ⏱️ **Multi-hour reindex times** that often never complete - 🔒 **Million+ row locks held simultaneously** (`1 056 250` measured on a 447k-product store) - 💥 **"Could not acquire lock for index"** errors when MySQL kills the transaction - 🪦 **Suspended scheduler state** in `indexer:status` requiring manual `indexer:reset` - 📉 **Drift between admin and storefront** - products added to categories never appear If you've ever seen `catalog_category_product` stuck at "Processing" in `indexer:status` while the schedule shows "suspended" - this is for you. The solution: Snapshot Pattern ------------------------------ [](#the-solution-snapshot-pattern) Instead of resolving EAV values on every reindex via `IFNULL(store, default)` joins, this module **pre-materializes** the EAV state into snapshot tables once: Snapshot tableReplacesBuilt when`simplemage_product_eav_snapshot`4× JOIN to `catalog_product_entity_int` (status + visibility)Before every full reindex; incrementally refreshed on partial (including `is_salable_composite`)`simplemage_category_eav_snapshot`2× JOIN to `catalog_category_entity_int` (is\_active)Same`simplemage_category_ancestor_map`Recursive walk over `catalog_category_entity.path`Before every full reindex; rebuilt on category-trigger partials (moves/creates)`simplemage_category_product_anchor_snapshot`Anchor expansion (parent category inherits products from descendants) - **store-aware**: per-store `is_active` of the assignment category, same semantics as core's per-store temp treeBefore every full reindex; refreshed per affected product on partialsThe actual reindex `INSERT...SELECT` then drops from **13 JOINs → 2 JOINs** (PK lookups against snapshot tables, with proper indexes), and the `WHERE` clause becomes a plain equality without `IFNULL` - letting the query optimizer pick a sane plan. Measured impact --------------- [](#measured-impact) All numbers below come from full-reindex benchmarks on copies of real production databases. In every run the index output was verified **byte-for-byte identical**to core Magento via full-content MD5 snapshots (every row, every column, every store view - no sampling). ### Magento 2.4.7-p7 - 111k products, 700+ categories [](#magento-247-p7---111k-products-700-categories) MetricCore MagentoThis moduleImprovement**Wall-clock time**124.4 s**16.2 s****7.7× faster**SQL queries6 9062 4422.8× fewerSlow queries3913511.2× fewerOutput identical to core-✅ MATCH (130 742 rows)### Magento 2.4.6-p14 - 447k products, 3 store views, anchor-heavy taxonomy [](#magento-246-p14---447k-products-3-store-views-anchor-heavy-taxonomy) MetricCore MagentoThis module**Wall-clock time**hangs (> 24 h, killed)**2 min 41 s**Row locks held simultaneously1 056 250 (growing)~few thousand (chunked)Tables locked simultaneously7 of 19 in use4-5JOINs in main `INSERT...SELECT`132EAV resolutionper-batch, via `IFNULL × 8`once, materialized### Mage-OS 2.2.1 (Magento 2.4.8-p4) - 503k products, 4 store views, anchor-heavy taxonomy [](#mage-os-221-magento-248-p4---503k-products-4-store-views-anchor-heavy-taxonomy) MetricCore MagentoThis moduleImprovement**Wall-clock time**42 min 8 s**16 min 21 s****2.6× faster**Peak memory84 MB52 MB1.6× lessSlow queries48202.4× fewerOutput identical to core-✅ MATCH (18 728 407 rows)### Adobe Commerce 2.4.7-p10 - 116k products, 2 store views, live scheduled updates [](#adobe-commerce-247-p10---116k-products-2-store-views-live-scheduled-updates) Correctness-only verification of the staging support added in 1.1.0 - no timings were recorded for this run. CheckResultFull reindex output vs core✅ MATCH (row count + per-store CRC32 fingerprints)Partial (mview) reindex output vs core✅ MATCHProduct with 3 live staging versions✅ exactly one snapshot row per storeContributed verification, [\#1](https://github.com/SimpleMage/magento2-category-product-indexer/pull/1). Your mileage will vary by catalog size, taxonomy shape, and MySQL/MariaDB tuning - but the architectural advantage holds across all measured workloads. Installation ------------ [](#installation) ### Via composer (recommended once published) [](#via-composer-recommended-once-published) ``` composer require simplemage/module-category-product-indexer bin/magento module:enable SimpleMage_CategoryProductIndexer bin/magento setup:upgrade bin/magento setup:di:compile bin/magento cache:flush ``` ### Manual installation (until published) [](#manual-installation-until-published) ``` # Drop the module into app/code/ mkdir -p app/code/SimpleMage cp -r app/code/SimpleMage/CategoryProductIndexer # Enable and compile bin/magento module:enable SimpleMage_CategoryProductIndexer bin/magento setup:upgrade bin/magento setup:di:compile bin/magento cache:flush ``` Usage ----- [](#usage) After installation the module takes over automatically - no configuration needed. Existing reindex commands work exactly the same: ``` # Full reindex of the (now-fast) catalog_category_product bin/magento indexer:reindex catalog_category_product # Or the linked pair bin/magento indexer:reindex catalog_category_product catalog_product_category # Or the whole indexer set bin/magento indexer:reindex ``` If you had a stuck indexer prior to installing this module, also run: ``` bin/magento indexer:reset catalog_category_product catalog_product_category ``` This clears any stale lock state from prior failed runs. ### Verifying it's active [](#verifying-its-active) ``` bin/magento dev:di:info "Magento\\Catalog\\Model\\Indexer\\Category\\Product\\Action\\Full" ``` Should report: ``` Preference: SimpleMage\CategoryProductIndexer\Model\Indexer\CategoryProduct\FullAction ``` ### Disabling (fallback to core) [](#disabling-fallback-to-core) If you ever need to revert to core Magento indexing (debugging, comparison, etc.): ``` bin/magento module:disable SimpleMage_CategoryProductIndexer bin/magento setup:di:compile bin/magento cache:flush ``` With the module merely *disabled*, the snapshot tables (`simplemage_*`) are kept on disk - harmless, and instantly reusable if you re-enable. A full `bin/magento module:uninstall SimpleMage_CategoryProductIndexer` drops them via `Setup/Uninstall.php`; after a manual (file-delete) removal, drop them yourself: ``` DROP TABLE IF EXISTS simplemage_product_eav_snapshot; DROP TABLE IF EXISTS simplemage_category_eav_snapshot; DROP TABLE IF EXISTS simplemage_category_ancestor_map; DROP TABLE IF EXISTS simplemage_category_product_anchor_snapshot; ``` What gets overridden -------------------- [](#what-gets-overridden) This module installs three `` rewrites in `etc/di.xml`: Core classReplacement`Magento\Catalog\Model\Indexer\Category\Product\Action\Full``SimpleMage\CategoryProductIndexer\Model\Indexer\CategoryProduct\FullAction``Magento\Catalog\Model\Indexer\Category\Product\Action\Rows``SimpleMage\CategoryProductIndexer\Model\Indexer\CategoryProduct\RowsAction``Magento\Catalog\Model\Indexer\Product\Category\Action\Rows``SimpleMage\CategoryProductIndexer\Model\Indexer\CategoryProduct\ProductCategoryRowsAction`All three replacements **extend** their core counterparts. Every overridden hook is gated on a per-run `snapshotReady` flag: when the snapshot build/refresh fails (lock wait timeout, disk full, DB error, etc.), the flag stays `false`, the failure is logged, and the entire run executes core's original EAV-JOIN implementations - so a failed snapshot never breaks indexing or corrupts output, it just runs slower. Compatibility ------------- [](#compatibility) - ✅ **Magento Open Source 2.4.6 - 2.4.9** (tested on 2.4.6-p14, 2.4.7-p7, and 2.4.8-p4 via Mage-OS 2.2.x) - ✅ **Adobe Commerce 2.4.6 - 2.4.9** including staging — EAV joins resolve the metadata link field (`row_id`) and all snapshot reads are built as framework `Select` objects, so the staging `FromRenderer` applies current-version filters (verified byte-identical on a 2.4.7-p10 EE catalog with live scheduled updates) - ✅ **Mage-OS 1.x / 2.x / 3.x** - `getVersion()` stays Magento-compatible, so detection below works unchanged - ✅ **PHP 8.2 / 8.3 / 8.4 / 8.5** - matches the PHP window of the supported Magento releases; CI runs the full matrix - ✅ **MySQL 8.0** and **MariaDB 10.4 - 10.11** - ✅ Multi-store, multi-website - ✅ Anchor categories - ✅ Configurable products (parent-child visibility resolution) - ✅ Disabled-product handling (respects `dev/indexer/include_disabled_products`) - ⚠️ Custom indexer extensions: any third-party module with its own `` on the same three core classes will conflict. Check before installing. ### Version-adaptive semantics [](#version-adaptive-semantics) Core changed `catalog_category_product` behavior between releases; the module mirrors the **installed** core so output stays byte-identical on every version: Core changeIntroducedHow the module adaptsNon-anchor + anchor selects filter category `is_active`2.4.8`CoreBehavior::filtersInactiveCategories()` - `version_compare()` on `ProductMetadataInterface::getVersion()` (Mage-OS reports the Magento-compatible version, e.g. 2.2.x → `2.4.8-p4`)`_tmp` operations must use the adapter that created the TEMPORARY table2.4.9Feature detection: `method_exists($tableMaintainer, 'getSameAdapterConnection')`For forks or backports where `getVersion()` is unreliable (e.g. git-dev installs reporting `UnknownVersion`), pin the behavior explicitly in `di.xml`: ``` true ``` Architecture ------------ [](#architecture) ### Class layout [](#class-layout) ``` SimpleMage/CategoryProductIndexer/ ├── etc/ │ ├── module.xml │ ├── di.xml ← 3 rewrites │ ├── db_schema.xml ← canonical snapshot-table declarations │ └── db_schema_whitelist.json ├── Model/ │ └── Indexer/ │ └── CategoryProduct/ │ ├── FullAction.php ← rewrites core Full (executeFull path) │ ├── RowsAction.php ← rewrites core Rows (category-change path) │ ├── ProductCategoryRowsAction.php ← rewrites core Rows (product-change path) │ ├── SnapshotBuilder.php ← builds + maintains snapshot tables │ ├── SnapshotAwareSelectsTrait.php ← shared Select-rewriting logic for Rows │ └── CoreBehavior.php ← version-adaptive core semantics (see Compatibility) ├── Setup/ │ └── Uninstall.php ← drops snapshot tables on module:uninstall ├── Test/ │ ├── Unit/ ← phpunit unit tests │ └── Integration/ ← phpunit integration tests (Magento test env) ├── composer.json ├── registration.php ├── LICENSE └── README.md ``` ### Reindex flow (Full) [](#reindex-flow-full) ``` ┌─────────────────────────────────────────────────────────────┐ │ FullAction::execute() │ └─────┬───────────────────────────────────────────────────────┘ │ ▼ ┌─────────────────────────────────────────────────────────────┐ │ SnapshotBuilder::ensureFresh() │ │ ├─ acquire GET_LOCK (blocking; timeout → core fallback) │ │ ├─ build simplemage_product_eav_snapshot (chunked, ~5k) │ │ ├─ build simplemage_category_eav_snapshot │ │ ├─ build simplemage_category_ancestor_map │ │ ├─ build simplemage_category_product_anchor_snapshot │ │ └─ release lock │ └─────┬───────────────────────────────────────────────────────┘ │ ▼ ┌─────────────────────────────────────────────────────────────┐ │ parent::execute() ← core Magento Full, but with rewritten │ │ Select objects that read from snapshot │ │ instead of EAV tables │ └─────────────────────────────────────────────────────────────┘ ``` ### Why "snapshot tables" are permanent (not temp) [](#why-snapshot-tables-are-permanent-not-temp) Unlike `temp_catalog_category_tree_index_*` which Magento drops/rebuilds on every run, our snapshot tables stay alive between reindexes. Reasons: 1. **Partial-reindex reuse** - `RowsAction` and `ProductCategoryRowsAction` refresh only the affected rows in the existing snapshot via `refreshForCategories()` / `refreshForProducts()` instead of rebuilding the whole thing. A temp table would be gone between calls. 2. **Forked-worker compatibility** - the snapshot must survive the `pcntl_fork()` that Magento's `ProcessManager` performs in batch mode. Per-connection temp tables wouldn't be visible in forked workers. ### Why a `trait` for the Rows path [](#why-a-trait-for-the-rows-path) `Magento\Catalog\Model\Indexer\Category\Product\Action\Rows` and `Magento\Catalog\Model\Indexer\Product\Category\Action\Rows` share Select-building logic via their common `AbstractAction` parent - but at a `protected` granularity that we cannot easily wrap or proxy. `SnapshotAwareSelectsTrait` is composed into both Rows replacement classes and provides shared snapshot-aware Select rewriters. The trait pattern lets us keep zero code duplication while still extending the right core classes individually. Testing ------- [](#testing) ``` # Run unit tests vendor/bin/phpunit -c app/code/SimpleMage/CategoryProductIndexer/Test/Unit/phpunit.xml.dist # Run integration tests (requires Magento test environment) dev/tests/integration/phpunit \ app/code/SimpleMage/CategoryProductIndexer/Test/Integration/ ``` The integration suite verifies that: 1. The di.xml preference resolves to our `FullAction` 2. A full reindex through the snapshot path terminates and produces non-empty per-store index tables 3. Two consecutive full reindexes produce **bit-identical** output (order-stable MD5 fingerprints per store) The module-vs-core bit-identical comparison (toggling the DI preference between runs) requires a separate process per DI configuration and lives in the companion `SimpleMage_IndexerBenchmark` module (`simplemage:bench:snapshot -d `) - results for real catalogs are listed under [Measured impact](#measured-impact). The unit suite additionally pins the SnapshotBuilder public contract, guards `etc/db_schema.xml` against drifting from the runtime DDL, and asserts every product-snapshot INSERT populates `is_salable_composite`. Known limitations ----------------- [](#known-limitations) - **Bit-identical module-vs-core verifier not yet automated in-repo.** Output is verified MD5-identical to core on real catalogs (see [Measured impact](#measured-impact)); the in-repo integration suite pins preference wiring, non-empty output, and run-to-run determinism, but the comparison runner that toggles core vs. snapshot mode needs a separate process per DI configuration and lives in the companion `SimpleMage_IndexerBenchmark` module. Tracked in [\#4](https://github.com/simplemage/magento2-category-product-indexer/issues/4). - **New store views require a full reindex.** Snapshot rows are materialised per existing store view; after creating a store view, run `bin/magento indexer:reindex catalog_category_product` once (core requires the same). License ------- [](#license) Released under the **MIT License** - see [LICENSE](LICENSE) for the full text. Contributing ------------ [](#contributing) Pull requests welcome. For substantial changes, please open an issue first to discuss what you'd like to change. When submitting: - Run `vendor/bin/phpcs` (Magento Coding Standard) - Run `vendor/bin/phpstan analyse` at level 8 - Add or update tests in `Test/Unit/` and/or `Test/Integration/` - Include a measurable performance number if your change touches the hot path Reporting bugs -------------- [](#reporting-bugs) If you hit *"Could not acquire lock"* or any other reindex failure with this module installed, please attach: 1. `bin/magento indexer:status catalog_category_product catalog_product_category` 2. Output of `SHOW ENGINE INNODB STATUS \G` from the time of failure 3. Approximate catalog size (`SELECT COUNT(*) FROM catalog_product_entity`), number of store views, and whether you use anchor categories 4. Magento version and PHP/MySQL versions Issues: ### Health Score 47 — FairBetter than 93% of packages Maintenance91 Actively maintained with recent releases Popularity23 Limited adoption so far Community11 Small or concentrated contributor base Maturity53 Maturing project, gaining track record Bus Factor1 Top contributor holds 75% of commits — single point of failure How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window. **Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores. **Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement. **Maturity (30%)** — Project age, version count, PHP version support, and release stability. ### Release Activity Cadence Every ~26 days Total 2 Last Release 43d ago ### Community Maintainers ![](https://avatars.githubusercontent.com/u/7571848?v=4)[Jakub Idziak](/maintainers/idziakjakub)[@idziakjakub](https://github.com/idziakjakub) --- Top Contributors [![idziakjakub](https://avatars.githubusercontent.com/u/7571848?v=4)](https://github.com/idziakjakub "idziakjakub (6 commits)")[![TuVanDev](https://avatars.githubusercontent.com/u/5556060?v=4)](https://github.com/TuVanDev "TuVanDev (2 commits)") --- Tags performancemagentocatalogindexermagento2category-productsnapshot-pattern ### Code Quality TestsPHPUnit Static AnalysisPHPStan Code StylePHP\_CodeSniffer Type Coverage Yes ### Embed Badge ![Health badge](/badges/simplemage-module-category-product-indexer/health.svg) ``` [![Health](https://phpackages.com/badges/simplemage-module-category-product-indexer/health.svg)](https://phpackages.com/packages/simplemage-module-category-product-indexer) ``` ### Alternatives [run-as-root/magento2-prometheus-exporter Magento2 Prometheus Exporter 69362.0k](/packages/run-as-root-magento2-prometheus-exporter)[smile/elasticsuite Magento 2 merchandising and search engine built on ElasticSearch 8034.8M53](/packages/smile-elasticsuite)[mollie/magento2 Mollie Payment Module for Magento 2 1142.0M17](/packages/mollie-magento2)[opengento/module-category-import-export This module add the capability to import and export the categories from the back-office. 1312.0k3](/packages/opengento-module-category-import-export)[mage-os/module-automatic-translation Automatic AI content translation for Mage-OS. 3223.7k](/packages/mage-os-module-automatic-translation)[angeo/module-llms-txt Magento 2 module for AI Engine Optimization (AEO). Generates spec-compliant llms.txt and llms-full.txt per llmstxt.org standard, plus streaming JSONL for vector indexing. Multi-store, multi-website, CLI, cron, async admin UI, Page Builder-aware sanitization, customer-group pricing, atomic writes, ETag/Cache-Control, .md mirrors. 132.2k](/packages/angeo-module-llms-txt) PHPackages © 2026 [Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)