Catalog and cart price rules: where the indexer really spends its time

Part 2 of the Catalog at Scale series — a rule that matches everything costs everything.

Catalog price rules look innocent in the admin: a condition, an action, a date range. Underneath, each rule compiles into SQL conditions evaluated against every matching product, and the result is materialized into catalogrule_product_price. When merchants stack dozens of overlapping rules on a 500k-SKU catalog, the catalog price indexer stops being a background job and becomes the longest pole in every deploy.

Two rule engines, two cost models

Catalog rules and cart rules are different systems that happen to share a UI pattern. Catalog rules rewrite the displayed price before the customer adds anything to the cart. Their output lands in catalogrule_product_price keyed by product, website, and customer group, and the price index tables consume that output when they compute the final price. Cart rules apply later, at quote time, through salesrule evaluation against the quote object in PHP.

That split decides where the cost lands. Catalog rules cost indexer time: every rule change invalidates the catalog rule index and forces recomputation over the affected products. Cart rules cost request time: every cart totals call loads the active rules and evaluates conditions against the quote. A catalog with forty catalog rules and a catalog with forty cart rules fail in completely different places, and conflating them leads to fixing the wrong layer.

How conditions become SQL

A catalog rule condition tree, category equals X, SKU matches a pattern, attribute set is Y, compiles into a product collection filter. Magento walks the condition tree and builds WHERE clauses against catalog_product_entity and the EAV value tables, then resolves the matching product IDs per website and customer group. Broad root conditions like "all products" or "category is any of the top-level categories" match hundreds of thousands of rows in one rule.

The expensive shapes are predictable: contains and like operators on varchar attributes that cannot use an index, conditions on text-backed attributes that force joins into catalog_product_entity_text, and deeply nested OR trees that defeat the query planner. Each additional OR branch is another subquery the database must union before the rule price can be written. Two precise rules with indexed equality conditions routinely index faster than one sprawling rule with five fuzzy branches.

Date ranges add a second dimension. A rule with a from and to date writes rows that are only valid inside that window, and the indexer must consider active windows per website. Rules left with open-ended dates "just in case" stay in every indexer run forever. Expired rules that were never disabled do the same. The active rule list is the indexer input, so pruning it is indexer optimization.

Reading catalogrule_product_price

When the catalog price index looks wrong, catalogrule_product_price is the first table to inspect. It holds one row per product, rule, website, and group combination with the computed rule price and the action details. Query it by product and website to see which rules matched: multiple rows for one product mean overlapping rules, and the rule sort order plus stop-processing flags decide which one wins.

Common findings at scale: the same product matched by six rules when the merchant intended one, a high-priority rule with stop-processing disabled so every lower rule also evaluates, and stale rows from rules that were deleted in the admin but whose index cleanup has not run. Count rows per rule to find the heavy hitters. A single rule responsible for 400k rows deserves a condition review before anything else.

Taming the indexer

Four practices keep rule indexing bounded:

• Fewer, sharper rules. Consolidate overlapping promotions into one rule with a clean condition instead of five rules that each match 80 percent of the catalog. Every rule eliminated removes a full matching pass.
• Equality over fuzzy match. Prefer is and is one of on indexed attributes like sku, category_ids, or attribute_set_id over contains on names and descriptions. If merchandising needs fuzzy matching, do it in search merchandising, not in price rules.
• Schedule by website and group. Rules scoped to one website and two customer groups index a fraction of the rows of a global rule. Scope is a performance control, not just a business control.
• Separate always-on from campaigns. Permanent price logic like "clearance category gets 10 percent off" should be few and stable. Time-boxed campaigns come and go; keep them disabled outside their window so the nightly indexer does not pay for them year-round.

Run the catalog rule indexer with timing per website on a production-shaped dataset and record the baseline. When merchants request a new rule, estimate its match count with a product collection query first. A rule matching 300k products is a capacity decision that deserves the same review as a schema change.

Cart rules: the quote-time cost

Cart rules do not touch catalogrule_product_price, but they have their own scaling trap. Each cart totals call loads all active cart rules for the website and customer group, then evaluates conditions against quote items, addresses, and totals. Rules with conditions on custom quote attributes or with many coupon codes increase per-request work linearly.

Keep the active cart rule count low, archive expired rules instead of leaving them enabled, and avoid conditions that call into slow custom logic during totals. Coupon codes with per-customer usage limits add a lookup per evaluation; thousands of single-use codes from an old campaign still load unless the rule is disabled. The quote is on the critical path of checkout, so cart rule hygiene is checkout latency work.

Debugging checklist

• List active catalog rules with match counts ordered by rows in catalogrule_product_price.
• Explain the compiled SQL of the top three rules and look for full scans on value tables.
• Verify stop-processing flags and sort order against merchant intent.
• Confirm expired and future-dated rules are disabled outside their windows.
• Time the catalog rule reindex per website before and after consolidation.

Price rules are business flexibility compiled into database work. The next post stays with pricing but moves to the other half: tier prices, group prices, and B2B resolution order.

Next in this series:

Continue with magento2-catalog-tier-group-pricing for tier pricing, customer groups, and B2B price resolution.