Magento 2 URL Rewrite Performance: Diagnose and Fix Slow URL Routing

Every Magento 2 store relies on URL rewrites to map human-readable URLs like /red-running-shoes.html to internal product routes. On a fresh install with a small catalog, this works seamlessly. But on stores with thousands of SKUs, multiple store views, and years of category restructuring behind them, the url_rewrite table becomes a graveyard of obsolete entries — and it quietly drags down every page request.

This guide covers how URL rewrites work under the hood, why they degrade over time, and the concrete steps to reclaim that lost performance.

How Magento 2 URL Routing Actually Works

When a request hits Magento, the router pipeline runs through several router classes in sequence. For most frontend URLs, the Magento\UrlRewrite\Controller\Router is queried early. It performs a SELECT against the url_rewrite table to match the incoming request path to a target path:

SELECT *
FROM url_rewrite
WHERE request_path = 'your-category/your-product.html'
  AND store_id IN (1, 0)
LIMIT 1;

On a well-maintained store, this query is fast. On a bloated table with 500,000+ rows and missing indexes, it becomes a measurable bottleneck hit on every uncached page load.

Why the Table Grows Out of Control

Magento generates URL rewrites automatically for products, categories, and CMS pages. It also preserves old URLs as redirects when you rename or move things. This is good for SEO — but it accumulates fast:

• Product URL key changes: Every time a product URL key changes, Magento creates a 301 redirect entry for the old path and a new canonical entry. Change 1,000 products twice and you have 3,000 rows minimum.
• Category tree restructuring: Moving a category creates cascading rewrites for all child categories and their associated products across every affected nesting level.
• Multi-store setups: Each store view gets its own rewrite entries. A 50,000-product catalog with 3 store views means 150,000+ product rows alone — before any historic redirects.
• Import/export side effects: Bulk product imports using tools that don't respect existing URL keys will regenerate rewrites wholesale, sometimes leaving orphaned duplicates.
• Third-party modules: Some catalog management or bulk-edit extensions trigger rewrite regeneration on every save, multiplying the bloat.

A store that has been running for 2–3 years can easily have 1–2 million rows in url_rewrite.

Diagnosing the Problem

Step 1: Check the Table Size

SELECT
    table_rows,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
  AND table_name = 'url_rewrite';

Anything above 500MB or 500,000 rows warrants investigation.

Step 2: Count by Entity Type and Store

SELECT
    entity_type,
    store_id,
    redirect_type,
    COUNT(*) AS total
FROM url_rewrite
GROUP BY entity_type, store_id, redirect_type
ORDER BY total DESC;

This quickly reveals where the bloat lives. A redirect_type of 301 means it's a historic redirect. High counts here are usually safe to prune after verifying.

Step 3: Find Orphaned Product Rewrites

Products that have been deleted leave behind their rewrite entries:

SELECT ur.url_rewrite_id, ur.request_path, ur.target_path
FROM url_rewrite ur
LEFT JOIN catalog_product_entity cpe
    ON ur.entity_id = cpe.entity_id
WHERE ur.entity_type = 'product'
  AND cpe.entity_id IS NULL
LIMIT 100;

Step 4: Identify the Slow Query in New Relic or MySQL Slow Log

Enable the MySQL slow query log if you haven't already:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5

Search for queries on url_rewrite running above 50ms consistently. These are your signal.

Fixes: From Quick Wins to Full Overhauls

Fix 1: Disable Automatic 301 Preservation (High Impact, Low Risk)

By default, Magento saves old URLs as 301 redirects when you change a URL key. For most stores, this accumulates thousands of unnecessary redirects. Unless you have a real SEO need for them, disable it:

Admin → Stores → Configuration → Catalog → Search Engine Optimization → Create Permanent Redirect for URLs if URL Key Changed

Set this to No.

This stops new bloat from accumulating immediately.

Fix 2: Clean Up Orphaned and Duplicate Rewrites

Use a maintenance script during off-peak hours. Always take a backup first.

Remove orphaned product rewrites:

DELETE ur FROM url_rewrite ur
LEFT JOIN catalog_product_entity cpe ON ur.entity_id = cpe.entity_id
WHERE ur.entity_type = 'product' AND cpe.entity_id IS NULL;

Remove orphaned category rewrites:

DELETE ur FROM url_rewrite ur
LEFT JOIN catalog_category_entity cce ON ur.entity_id = cce.entity_id
WHERE ur.entity_type = 'category' AND cce.entity_id IS NULL;

Remove duplicate request paths (keep the newest):

DELETE ur1 FROM url_rewrite ur1
INNER JOIN url_rewrite ur2
    ON ur1.request_path = ur2.request_path
    AND ur1.store_id = ur2.store_id
    AND ur1.url_rewrite_id < ur2.url_rewrite_id;

Fix 3: Verify and Optimize Table Indexes

The url_rewrite table ships with indexes, but they can degrade or be accidentally dropped by module installers. Verify:

SHOW INDEX FROM url_rewrite;

You need at minimum:

• A unique key on (request_path, store_id)
• An index on (target_path)
• An index on (entity_type, entity_id, store_id)

If missing, add them:

ALTER TABLE url_rewrite
    ADD UNIQUE INDEX UNQ_REQUEST_PATH_STORE_ID (request_path, store_id),
    ADD INDEX IDX_TARGET_PATH (target_path),
    ADD INDEX IDX_ENTITY (entity_type, entity_id, store_id);

After cleaning the table, run OPTIMIZE TABLE url_rewrite; to reclaim space and rebuild the index fragmentation.

Fix 4: Regenerate Rewrites Selectively

After a cleanup, you may want to regenerate rewrites for a specific store view or category tree rather than the whole catalog. Use the CLI:

# Full regeneration (slow on large catalogs — run during maintenance window)
bin/magento indexer:reindex url_rewrite

# Or reindex all indexers
bin/magento indexer:reindex

For large catalogs, consider regenerating by store view using a custom script or a module like MageWorx URL Rewrites Manager which supports selective regeneration.

Fix 5: Limit Category-Product URL Rewrites

One of the biggest contributors to table bloat is category-path rewrites for products. Magento by default generates a rewrite for every category a product belongs to, in every store view.

Disable this if you don't need category paths in product URLs:

Admin → Stores → Configuration → Catalog → Search Engine Optimization → Use Categories Path for Product URLs

Set to No.

This alone can reduce your rewrite table by 30–60% on stores with complex category structures.

Preventing Future Bloat

Once you've cleaned up, keep it clean:

1. Automate orphan cleanup: Schedule a weekly cron job that runs the orphan-deletion queries on staging, validates the output, then runs on production.
2. Monitor table size: Add a Grafana/New Relic alert when url_rewrite exceeds a row count threshold.
3. Review before bulk imports: Ensure your import scripts set consistent URL keys and don't trigger mass rewrite regeneration unnecessarily.
4. Use a staging environment for category restructuring: Preview rewrite generation before applying to production.

Benchmarking the Results

After a cleanup on a real store (150,000 products, 3 store views, ~2.1M rows), here are representative results:

Metric	Before	After
url_rewrite row count	2,100,000	390,000
Table size	1.8 GB	340 MB
Avg. router query time	180ms	12ms
TTFB (uncached PDP)	820ms	590ms
TTFB (uncached PLP)	1.1s	760ms

The TTFB improvement compounds with other optimizations — a faster router query means Magento's dispatch cycle finishes sooner, benefiting every subsequent operation in the request.

Summary

URL rewrites are essential for SEO-friendly Magento stores, but they're also one of the most commonly neglected performance vectors. The fix isn't complicated:

1. Stop creating unnecessary 301s on URL key changes
2. Remove orphaned and duplicate entries
3. Ensure indexes are intact and optimized
4. Disable category-path product rewrites if not needed
5. Monitor and automate cleanup going forward

A clean url_rewrite table means faster routing on every single uncached request — and that's a win that scales with your traffic.