# 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.

`composer require simplemage/module-category-product-indexer`

Canonical URL: https://packagento.com/simplemage/module-category-product-indexer

## At a glance

- **Vendor**: simplemage (https://packagento.com/simplemage.md)
- **Latest version**: v1.1.0 — released 2026-07-09
- **Pricing**: Free
- **Package type**: Magento 2 module
- **Status**: active, accepting new buyers

## Installation

Packagento is licence-gated, so even free packages need a licence on a project before Composer can resolve them.

1. **Sign in or create an account** at https://packagento.com/customer/account/.

2. **Add the package to your account.** Open https://packagento.com/simplemage/module-category-product-indexer and complete the free checkout. A licence is minted automatically.

3. **Create or pick a project, then activate the licence on it.**
   - Projects represent the Magento installs you deploy to. Manage them at https://packagento.com/projects/.
   - Activate the new licence on the project you'll deploy this package to. Activation is what generates the Composer credentials scoped to that project.

4. **Add the project credentials to your Magento codebase.**

   Grab the project's public + private key from https://packagento.com/projects/ (open the project, then its Credentials tab), and add them to `auth.json`:

   ```json
   {
     "http-basic": {
       "packagento.com": {
         "username": "ppk_live_...",
         "password": "psk_live_..."
       }
     }
   }
   ```

   Add the Packagento Composer repository to `composer.json`:

   ```json
   {
     "repositories": [
       { "type": "composer", "url": "https://packagento.com" }
     ]
   }
   ```

5. **Install and apply.**

   ```bash
   composer require simplemage/module-category-product-indexer:*
   bin/magento setup:upgrade
   bin/magento setup:di:compile
   bin/magento cache:flush
   ```

## What it does

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.

## README

[![License: MIT](https://img.shields.io/badge/License-MIT-blue.svg)](LICENSE)
[![Magento 2.4](https://img.shields.io/badge/Magento-2.4-orange.svg)](https://magento.com/)
[![PHP 8.2-8.5](https://img.shields.io/badge/PHP-8.2--8.5-777BB4.svg)](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

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

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 table | Replaces | Built 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 tree | Before every full reindex; refreshed per affected product on partials |

The 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

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

| 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 identical to core | - | ✅ MATCH (130 742 rows) | |

#### 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, anchor-heavy taxonomy

| 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 identical to core | - | ✅ MATCH (18 728 407 rows) | |

#### Adobe Commerce 2.4.7-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.

| Check | Result |
|---|---|
| Full reindex output vs core | ✅ MATCH (row count + per-store CRC32 fingerprints) |
| Partial (mview) reindex output vs core | ✅ MATCH |
| Product with 3 live staging versions | ✅ exactly one snapshot row per store |

Contributed 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

#### Via composer (recommended once published)

```bash
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)

```bash
## Drop the module into app/code/
mkdir -p app/code/SimpleMage
cp -r <this-repo> app/code/SimpleMage/CategoryProductIndexer

_(README truncated for .md surface. Full README on https://packagento.com/simplemage/module-category-product-indexer.)_

## Changelog

All notable changes to this project are documented in this file.

The format is based on [Keep a Changelog](https://keepachangelog.com/en/1.1.0/),
and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0.html).

### [1.1.0] - 2026-07-09

Adobe Commerce (staging) support. Open Source and Mage-OS behaviour is unchanged —
verified byte-for-byte on a 447k-product catalog (see *Verified* below).

#### Added

- **Adobe Commerce (staging) compatibility** ([#1](https://github.com/SimpleMage/magento2-category-product-indexer/pull/1), thanks [@TuVanDev](https://github.com/TuVanDev))
  - EAV joins and `catalog_product_relation.parent_id` now resolve the entity link
    field through `MetadataPool` (`row_id` on Adobe Commerce, `entity_id` on Open
    Source), mirroring core `AbstractAction`.
  - Every snapshot `INSERT ... SELECT` (product, category, and the composite-salability
    subquery) is built as a framework `Select` object instead of a raw SQL string, so
    Adobe Commerce's staging `FromRenderer` injects the `created_in`/`updated_in`
    current-version filters at assemble time. Raw SQL bypassed that renderer and would
    have read every historical version of an entity with a scheduled update.

#### Fixed

- **Snapshot key joins on Adobe Commerce.** Three joins in `SnapshotAwareSelectsTrait`
  matched the snapshot tables against the metadata link field (`cp.row_id`) instead of
  the stable `entity_id` they are keyed by, silently dropping products whose `row_id`
  diverges from `entity_id`. No effect on Open Source, where the two are identical.

#### Changed

- **`SnapshotBuilder::__construct()` now requires `MetadataPool`.** Autowired by DI —
  no `di.xml` changes needed — but run `bin/magento setup:di:compile` after upgrading.
  Technically breaking for anyone extending `SnapshotBuilder` directly.

#### Verified

- **Magento Open Source 2.4.6-p14** — 447,730 products, 538 categories, 3 store views:
  full-reindex index output **byte-for-byte identical to 1.0.0** (676,395 rows, matching
  MD5), identical query profile (421 queries, 208 slow queries, 8 temp tables), no
  performance regression (212.3 s vs 214.8 s), partial reindex idempotent, 22/22 unit
  tests green.
- **Adobe Commerce 2.4.7-p10** — 116k products, 2 store views, live scheduled updates:
  full and partial reindex output byte-identical to core; a product with three live
  staging versions yields exactly one snapshot row per store.

### [1.0.0] - 2026-06-12

Initial public release.

#### Added

- Drop-in replacement for Magento 2's `catalog_category_product` indexer using the
  snapshot pattern: EAV state is materialised once into helper tables, cutting the main
  `INSERT ... SELECT` from 13 JOINs to 2.
- Full reindex (`FullAction`) plus both partial-reindex paths (`RowsAction` for
  category triggers, `ProductCategoryRowsAction` for product triggers).
- Store-aware anchor snapshot reproducing core's per-store `is_active` semantics.
- Version-adaptive core behaviour (`CoreBehavior`): mirrors the inactive-category
  filtering introduced in 2.4.8 and the `getSameAdapterConnection()` temp-table
  handling introduced in 2.4.9.
- Genuine fallback to core: every overridden hook is gated on a per-run `snapshotReady`
  flag, so a failed snapshot build logs and runs the stock EAV-JOIN path rather than
  producing wrong output.
- Declarative schema (`etc/db_schema.xml` + whitelist) and `Setup/Uninstall.php`.
- Blocking advisory lock serialising all snapshot writes.

#### Performance

Measured against core Magento on real production databases: **2.6×–7.7× faster full
reindex**. A 447k-product store where core never completed a reindex now finishes in
2 min 40 s. Output verified MD5-identical to core.

[1.1.0]: https://github.com/SimpleMage/magento2-category-product-indexer/compare/v1.0.0...v1.1.0
[1.0.0]: https://github.com/SimpleMage/magento2-category-product-indexer/releases/tag/v1.0.0

## Recent Versions

| Version | Released |
|---|---|
| v1.1.0 | 2026-07-09 |
| v1.0.0 | 2026-06-12 |

## Dependencies

### Require

| Package | Constraint |
|---|---|
| magento/framework | >=103.0.6 |
| magento/module-catalog | >=104.0.6 |
| magento/module-indexer | >=100.4.6 |
| php | ~8.2.0 \|\| ~8.3.0 \|\| ~8.4.0 \|\| ~8.5.0 |

### Require (dev)

| Package | Constraint |
|---|---|
| magento/magento-coding-standard | >=33 <40 |
| phpstan/phpstan | ^1.10 \|\| ^2.0 |
| phpunit/phpunit | ^10.5 \|\| ^11.0 |
| squizlabs/php_codesniffer | ^3.7 |

### Suggest

| Package | Constraint |
|---|---|
| simplemage/module-indexer-benchmark | Dev-only companion harness: before/after benchmarks and byte-identity verification against core (staging/dev environments only) |

## Quality

Latest release (v1.1.0) fails the Packagento QA pipeline. Verdicts below are per-cell (Magento line × PHP version) for the matrixed tools, and run-once for the static / security tiers.


### Compatibility

Each Magento line is installed on its supported PHP versions, then the module is built (DI compile + static-content deploy). Cells show passed / failed / untested; staircase gaps render as `–`.

| Magento | PHP 8.2 | PHP 8.3 | PHP 8.4 | PHP 8.5 |
|---|---|---|---|---|
| 2.4.7 | Pass | Pass | – | – |
| 2.4.8 | – | Pass | Pass | – |
| 2.4.9 | – | – | Pass | Pass |


### Code Quality

Advisory checks against the module's source. Never affect the Compatibility verdict — a phpcs finding can't make a module incompatible.

#### Static Analysis

Coding standards (phpcs), mess detection (phpmd), copy-pasted code (cpd), PHP cross-version compatibility, composer.json validity. Each runs once for the whole module.

| Tool | Status | Findings | Summary |
|---|---|---|---|
| PHPCS | Warning | 23 | 23 warnings (ruleset: Magento2) |
| PHPMD | Warning | 7 | 7 rule violations (UnusedPrivateField:7) |
| Cpd | Warning | 1 | 1 duplicated chunk spanning 55 total lines (min-lines=5, min-tokens=70) |
| Composer validate | Info | 3 | valid; 3 advisory notes (composer validate --strict) |

#### PHPStan

Type-checks the module against a real Magento install. Re-runs per Magento + PHP version because resolvable symbols differ between releases.

| Magento | PHP 8.2 | PHP 8.3 | PHP 8.4 | PHP 8.5 |
|---|---|---|---|---|
| 2.4.7 | 6 | 6 | – | – |
| 2.4.8 | – | 6 | 6 | – |
| 2.4.9 | – | – | 7 | 7 |


### Tests

Unit and integration suites run per Magento + PHP cell. Test failures speak to the module's behaviour, not its compatibility with a line, so they're reported here separately.

#### Unit Tests

| Magento | PHP 8.2 | PHP 8.3 | PHP 8.4 | PHP 8.5 |
|---|---|---|---|---|
| 2.4.7 | Pass | Pass | – | – |
| 2.4.8 | – | Pass | Pass | – |
| 2.4.9 | – | – | 1 | 1 |


### Security

Dependency-advisory audit (composer audit) plus a source malware scan. A malware detection fails the version outright.

| Tool | Status | Findings | Summary |
|---|---|---|---|
| Composer audit | Pass | 0 |  |
| Malware scan | Pass | 0 |  |

## Licence and pricing

Free. A licence is still minted on checkout and bound to your project for Composer access — no payment step.

Refundable within 14 days of first purchase via https://packagento.com/account/refunds/.

## Install via Claude Code or any MCP client

The Packagento MCP server can run the licence + project + Composer steps above in one tool call:

```
purchase_and_install_packages(
  composer_names=["simplemage/module-category-product-indexer"],
  project_id="proj_xxx"
)
```

This handles cart, checkout, licence minting, project activation, and writes auth.json credentials. Connect a client with `claude mcp add packagento https://mcp.packagento.com`. Full setup at https://packagento.com/docs/mcp-setup.

## Vendor

simplemage is a Magento 2 vendor on Packagento. See https://packagento.com/simplemage.md for their full catalogue.