Magento 2 URL Rewrites Cleanup: Shrink the url_rewrite Table

On many older Magento 2 stores the url_rewrite table has millions of rows for a catalog of a few thousand products. It slows down category and product saves, makes imports drag, and bloats every database dump. This article explains where the rows come from in Magento Open Source and Adobe Commerce 2.4.8, how to measure the problem, and how to clean it up without breaking URLs that search engines and customers still use.

How Magento builds URL rewrites

Every storefront URL that is not a raw controller path is resolved through url_rewrite. Each row has a request_path, a target_path, a redirect_type (0 for an internal rewrite, 301 or 302 for redirects), a store_id, an entity_type and entity_id, and an is_autogenerated flag. A second table, catalog_url_rewrite_product_category, links product rewrites that include a category path to that category.

For a single product Magento can generate:

• one rewrite for the product URL without category, per store view
• one rewrite per category the product is in, with the category path in front, per store view, when category paths in product URLs are enabled
• a 301 redirect for every old URL key, per store view, when permanent redirects on URL key change are enabled

Multiply by store views and by categories, and add history from every URL key change and every category move, and the numbers grow fast. Moving a category with many children and products generates redirects for the category, all of its subcategories and all product URLs that contained its path.

The settings that control growth

These are under Stores, Configuration, Catalog, Catalog, Search Engine Optimization:

Setting	Path	Effect
Use Categories Path for Product URLs	catalog/seo/product_use_categories	Whether product links on the storefront use the category path
Generate "category/product" URL Rewrites	catalog/seo/generate_category_product_rewrites	Whether rewrites with category paths are generated at all
Create Permanent Redirect for URLs if URL Key Changed	catalog/seo/save_rewrites_history	Default for keeping 301s when a URL key changes

The second setting is the big one. Category path rewrites are usually most of the table. If the store links products by their short URL and the canonical tag points there, those rows are not needed. The admin warns that setting it to No removes the existing category path product rewrites. That is the point, but it also means those URLs stop resolving. Check first whether they were ever linked or indexed.

The canonical link settings in the same section decide what search engines see as the primary URL. The broader picture is in the technical SEO checklist.

Measure before deleting

Start with counts by 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;

Then compare product rewrites against products. A ratio of dozens of rewrites per product points to category paths or redirect history:

SELECT
  (SELECT COUNT(*) FROM url_rewrite WHERE entity_type = 'product') AS product_rewrites,
  (SELECT COUNT(*) FROM catalog_product_entity) AS products;

Rewrites that point to products that no longer exist are pure waste. Magento normally removes rewrites when a product is deleted, but products removed with SQL or by broken modules leave them behind:

SELECT COUNT(*)
FROM url_rewrite ur
LEFT JOIN catalog_product_entity p ON p.entity_id = ur.entity_id
WHERE ur.entity_type = 'product'
  AND p.entity_id IS NULL;

Run the same check for categories with catalog_category_entity and for cms-page with cms_page.page_id. Also look for product rewrites of products with visibility Not Visible Individually. Those products have no page of their own and should not have rewrites.

Cleaning up safely

Work on staging with a recent copy of production first, and take a backup of url_rewrite and catalog_url_rewrite_product_category before touching production. A table level dump is enough to restore in minutes.

mysqldump --single-transaction magento url_rewrite catalog_url_rewrite_product_category \
  | gzip > url_rewrite_backup.sql.gz

1. Remove orphans

Delete rewrites for entities that do not exist anymore. This has no SEO cost, because those URLs already return a 404 page or should.

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

On Adobe Commerce the join is still on entity_id, since url_rewrite stores the entity ID and not the staging row ID.

2. Decide on category path rewrites

If product URLs with category paths were never linked on the storefront, which is the case when Use Categories Path for Product URLs was always No, they bring nothing and can go. Turn off the generate setting and let Magento remove them. If they were linked in the past, check your analytics and Search Console for traffic to those URLs. For URLs that still get visits, keep 301 redirects to the short product URL instead of dropping them.

3. Trim redirect history carefully

Old 301 redirects protect inbound links. Deleting them all is a quick win for table size and a slow loss in traffic. A reasonable rule is to keep redirects that have received traffic in the last year and remove the rest. You need access logs or analytics data to do this properly. Without data, I leave redirect history alone.

Custom redirects created by hand in Marketing, URL Rewrites have is_autogenerated set to 0. Never include those in bulk deletes. They were added on purpose.

4. Regenerate where needed

Magento has no core command to regenerate all URL rewrites. Rewrites are generated when a product or category is saved. For a full regeneration, the community module elgentos/regenerate-catalog-urls adds commands to regenerate product, category and CMS page rewrites from the command line. It is widely used, but test it on staging, because regenerating rewrites for a large catalog takes time and replaces the generated rows.

Keeping it small

• Decide the URL structure early: short product URLs with canonical tags are the simplest setup.
• Avoid moving large categories around on a live store without thinking about the redirects that follow.
• Do not change URL keys in bulk imports unless the change is intended. Each change creates redirects on every store view.
• Use as few store views as the business needs. Each store view multiplies every rewrite.
• Check the table size every few months with the queries above.

Rewrite problems often show up together with other catalog issues. When product saves are slow, also check indexer modes and third party observers, as described in common Magento 2 errors and fixes.

Errors related to rewrites

The most common one is "URL key for specified store already exists". It appears on product or category save and on import when another entity already uses the same request_path on that store. Sometimes the conflicting row belongs to an entity that no longer exists, which is another reason to remove orphans. Find the conflict with a query on request_path and store_id before changing data.

FAQ

Can I truncate the url_rewrite table in Magento 2?

Only if you regenerate everything right after and accept losing all redirect history and custom redirects. On a live store that means broken inbound links. Targeted deletes are safer.

Why does saving a category take so long?

Saving or moving a category regenerates rewrites for the category, its children and the products with category paths, on every store view. A bloated url_rewrite table makes that much slower.

Should product URLs include the category path?

For most stores, no. Short product URLs avoid duplicate content and keep the rewrite table small. Category paths only add value if the business really wants them in the visible URL.

Does Magento remove rewrites when a product is deleted?

Yes, when the product is deleted through Magento. Products removed with direct SQL or by modules that skip the normal delete leave orphan rewrites behind.

I am available for freelance Magento and Adobe Commerce work, including database cleanup and technical SEO. If you need help with this, get in touch.