Slow Magento reindexes are a price index problem, and raw SQL makes it worse

A walkthrough published on dev.to argues that when a client asks why their Magento 2 reindex is slow, the answer is almost always the price index: it is the indexer that scales worst with catalog size, and it chokes hardest on B2B stores and configurable-heavy catalogs ^[1]. The same piece makes the point that matters operationally, which is that the index gets more painful the more you try to fix it with raw SQL ^[2].

The reason is structural. The raw price you type into the admin lands in `catalog_product_entity_decimal`, one row per product, attribute, store and scope ^[3]. But the effective price stops being a column lookup the moment you add tier prices, scheduled special prices, catalog price rules, customer group prices, bundle or grouped composite pricing, configurable products with per-option adjustments, or Content Staging updates ^[4]. Because the answer depends on time, customer group and rule conditions, Magento has to precompute it ^[5]. When the precomputation is current, a page request reads flat, pre-joined rows; when it is stale or under-built, you either serve wrong prices or trigger expensive on-the-fly calculation ^[6].

The build itself writes to `catalog_product_index_price`, plus `_idx` and `_tmp` variants and the `catalog_product_index_price_final_tmp` intermediate table ^[7]. It runs in phases: load every product into the temp table at base price, apply tax, tier, special, group and rule adjustments, resolve composite products (bundle min/max, grouped sums), then merge into the final table with its customer group, website and store dimension rows ^[8].

That dimension space is where the cost lives. One row per product times website times customer group means a 50,000 SKU store on two websites with four customer groups produces 400,000 price index rows ^[9][10], or eight rows per SKU before anything else ^[1]. Configurable option permutations and staged versions multiply it further ^[11], and a configurable product with 50 options effectively fans out into 50 price rows minus on-demand fallbacks ^[12].

The rule engine is the other half. Unlike tier prices, which are stored per row, catalog price rules are evaluated by an engine that tests each product against each rule's conditions, which is a full-catalog rules evaluation rather than a read ^[13]. The MySQL-based `catalogrule` engine is slow and the indexer runs it on every reindex ^[14]; according to the guide this is the single most common reason a price reindex takes minutes to hours on real stores ^[15].

Partial reindexing does not save you either. A full `indexer:reindex catalog_product_price` rebuilds the entire dimension space ^[16], and the price indexer historically had weak partial support, so one product save could kick off a large portion of the rebuild ^[17]. Mview helps, but on write-heavy stores it falls behind, and once it falls too far behind Magento drops into a synchronous rebuild mid-request ^[18]. Underneath all of it, the indexer builds large temporary tables, sorts them and merges them, which is brutal on an under-provisioned MySQL with a small `innodb_buffer_pool_size` and no SSD ^[19], and it cannot easily be parallelised by default ^[20].

The diagnostic order the piece recommends is worth copying: check the schedule status for `catalog_product_price`, time a full build, then count rows in `catalog_product_index_price` and `catalog_product_index_price_final_tmp` ^[21]. If the row count sits at five to ten times your SKU count, the problem is multiplication across websites, groups and option permutations, and no amount of server tuning will fix it ^[22].

Worth watching on your own stores: the ratio of index rows to SKUs, and whether mview is quietly behind, because both determine whether the next slow page load is a cache miss or a full rebuild happening inside a customer's request.