As time goes by, Magento Enterprise collects data that, in fact, is unnecessary, yet makes database, namely the 'enterprise_url_rewrite', 'enterprise_url_rewrite_redirect', and 'enterprise_url_rewrite_redirect_rewrite' Magento tables, less agile and efficient, and makes the shop process more information and return slower results. In our humble opinion, this is not ok. So here we are presenting a way out.
Duplicating URL keys of products and categories is what makes the url-rewrite tables expand. Specifically, Magento Enterprise adds sequence numbers to duplicated URLs transforming them into 'product-6.html', 'product-7.html', etc.
The end of the story? Nope. Once admin wants to sate any product or execute a 'Catalog URL Rewrites' reindex, all URLs he remembers from before get renumbered again:
product-8.html -> product-10.html -> product-12.html
product-9.html -> product-11.html -> product-13.html
Even more, in order to make the old URLs to work Magento adds several redirect records in 'enterprise_url_rewrite_redirect' and 'enterprise_url_rewrite_redirect_rewrite' tables:
product-8.html -> product-10.html
product-9.html -> product-11.html
product-10.html -> product-12.html
product-11.html -> product-13.html
Moving forward. With multi-category products the problem gets even worse. Do you like geometric series? This is exactly what happens here: a number of DB records is multiplied by a number of the product categories.
This occurs every time reindex is run and a product / category is edited. The tables get inflated without a break, a reindex procedure becomes slower, and a number of the DB entries increase by almost half.
How it works
1) Let's remove excess redirects
• System > Configuration > SafeMage: URL Rewrites Optimization > Clearing Settings of the enterprise_url_rewrite...-related Tables
For most admin users, we would recommend sticking with the 'Remove All Excess Redirects' option under Clearing Mode. Don't get cautious thinking Google and visitors of your shop won't find any product that was indexed prior to this optimization. For this very case, we got a special setting to turn on:
• System > Configuration > SafeMage: URL Rewrites Optimization > Routing of Pages Ending with a Digit: Enable Routing = Yes
It reads: if an old URL like [product_url_key]-146833 or [category_url_key]-834 is considered as 404 by your Magento, then the module is ordered to try to locate the same URL minus digits at its end. If the result is positive, it enables 301 redirect for it:
product-146833.html > product.html
category-834.html > category.html
2) Now you got some data to reindex
• System > Index Management > Catalog URL Rewrites > Reindex Data
The 'enterprise_url_rewrite' table is going to ignore URLs of 'Disabled' or 'Not Visible Individually' products, inactive categories, or disabled store views. This allows the table to remarkably shrink its size, even better if the store consists of numerous Configurable or Bundle products. A rule to remember: running reindex via SSH is recommended if the table includes loads of entries:
php shell/indexer.php --reindex catalog_url
3) Let us emphasize that the extension is not a one-time pill to swallow. When admin installs it, it fixes important Magento bugs that affect Catalog URL Rewrites reindex or the result of saving of product / category. It guarantees the 'enterprise_url_rewrite', 'enterprise_url_rewrite_redirect' and 'enterprise_url_rewrite_redirect_rewrite' tables won’t increase in size every time if not necessary.
4) Admin can enable 'Unique URL Key Verification' setting to prevent backend users from entering and saving non-unique URL keys.
5) To even harder squeeze the tables, admin can remove categories from product URLs. To do so, disable the setting below and run Catalog URL Rewrites reindex.
• System > Configuration > CATALOG: Catalog > Search Engine Optimizations > Use Categories Path for Product URLs
Enough theory! What is this tool really capable of? Without bragging, we managed to decrease the number of table records from rough 27 million to fluffy 45 thousand (600 times less). The table has remained on the similar size level until now.
- Guarantees minimal size of the 'enterprise_url_rewrite', 'enterprise_url_rewrite_redirect', and 'enterprise_url_rewrite_redirect_rewrite' tables (some stores may expect to get 10000 times less records)
- Fixes some nasty problems caused by Magento Enterprise itself that surface during Catalog URL Rewrites reindex or when saving product / category
- Features unique URL key verification for products / categories
- Allows to restore URLs ending with digits
- Enables optimization by cron schedule
- Enables checking and clearing of the log tables
CompatibilityMagento EE 1.9.x, 1.10.x, 1.11.x, 1.12.x, 1.13.x, 1.14.x
We provide comprehensive product support for all the Magento extensions available on this site starting from the moment of your purchasing them. If you need help on the extensions installation or on any issues relating to our products, our dedicated team of proven technical experts is at your service, ready to assist you at any time.
Changelognew feature bugfix
- v.1.4.0 (Jan 16, 2017)
- Added Restore and Reindex buttons.
- v.1.3.0 (Dec 10, 2016)
- Added additional information about Log Tables;
- Code improvements.
- v.1.2.0 (Dec 7, 2016)
- Added automatic clearing by Cron.
- v.1.1.0 (Dec 6, 2016)
- Added new clearing mode 'Remove only with Digit at End'.
- v.1.0.0 (Sep 29, 2016)
- Initial release.