Use canonical category id_path equality instead of broad category/% predicate in URL rewrite joins

[loekvangool]

Summary

Category URL rewrite joins currently use id_path LIKE 'category/%'. This is broader than needed for canonical category rewrites and can force much higher inner-loop row scans.

Replacing with canonical equality:

id_path = CONCAT('category/', category_id)

makes the join sargable on the full key pattern and avoids matching non-canonical category/% paths.

Why this matters

From ANALYZE FORMAT=JSON on a representative workload, the inner lookup cost drops from roughly ~80 to ~2 (about 40x cheaper for that probe pattern). The unoptimized query does X loops of the core_url_rewrite table, where X is the number of active categories. The optimized query brings this down to 1 loop.

Correctness / risk

• On valid data, both predicates return identical results.
• Divergence is still possible in systems with inconsistent URL rewrite rows (e.g. category_id not matching the numeric suffix in id_path).
• Important nuance: if data is in that inconsistent state, result differences are a symptom that URL rewrite data cannot be trusted without cleanup anyway.

Operational remediation for inconsistent data

If inconsistent rows exist, this usually self-heals by:

1. Re-saving affected category/categories.
2. Reindexing URL rewrites.

Local quick scan

I re-scanned local code and only found category LIKE 'category/%' hotspots in:

• app/code/core/Mage/Catalog/Helper/Category/Url/Rewrite.php
• app/code/core/Mage/Core/Model/Resource/Url/Rewrite/Collection.php

(plus a separate, unrelated product/% usage).

Proposed change

Switch the category predicates in the above two files from LIKE 'category/%' to canonical equality on category_id.