It’s a matter of common knowledge that with time the Magento 'core_url_rewrite' table continuously increases in size, which in turn affects website’s performance and reduces processing efficiency of data bases. We’ve figure out the source of the problem and developed a sublime solution for you.
The reason for increasing size of the foregoing table is duplicating/intersecting URL Keys of products and categories. To be more specific, default Magento renames duplicated URLs into 'product-1.html', 'product-2.html', and so on, by adding a respective sequence number.
Surprisingly, whenever admin user tries to save a product or run 'Catalog URL Rewrites' reindex, all URLs generated before get suffixed again without any reasonable cause:
product-1.html -> product-3.html -> product-5.html
product-2.html -> product-4.html -> product-6.html
Additionally, for the previous URL to work, Magento’s code adds several redirect records:
product-1.html -> product-5.html
product-3.html -> product-5.html
product-2.html -> product-6.html
product-4.html -> product-6.html
As if that wasn't enough, in case of multi-category products, a number of the records is multiplied by a number of the related categories. How about geometric series here?
With every reindex or product/category edit, this happens infinitely. Just think of it for a moment: the table gets continuously pumped, a reindex operation takes forever to finish, and records increase by almost half.
How it works
1) To start with, admin user is to get rid of excess redirects
• System > Configuration > SafeMage: URL Rewrites Optimization > Clearing Settings of 'core_url_rewrite' Table
In most cases, we'd recommend choosing the 'Remove All Excess Redirects' option under Clearing Mode. You should not be anxious about Google or your customers not finding a previously indexed product thanks to the setting:
• System > Configuration > SafeMage: URL Rewrites Optimization > Routing of Pages Ending with a Digit: Enable Routing = Yes
What this means is, if an old URL like [product_url_key]-297823 or [category_url_key]-1126 is recognized by your Magento as 404, the extension then tries to find such an URL without digits at its end, and, if succeeded, enables 301 redirect for it:
product-297823.html > product.html
category-1126.html > category.html
2) Next, it's time to reindex data
• System > Index Management > Catalog URL Rewrites > Reindex Data
URLs of 'Disabled' or 'Not Visible Individually' products, inactive categories, or disabled store views will not be added to 'core_url_rewrite'. This will enable to decrease the table size even more significantly, especially if the store includes many Configurable or Bundle products. Note that if the final table includes too many entries you'd better run reindex via SSH:
php shell/indexer.php --reindex catalog_url
3) There is something to be accentuated: the extension is not a one-time pill for your system. It automatically fixes several Magento bugs during Catalog URL Rewrites reindex or while saving product/category. So the table won’t be increasing in size without real necessity.
4) A handy 'Unique URL Key Verification' setting can be enabled to prevent admin user to enter duplicated URL keys.
5) To get even more out of the optimization, you can exclude categories in product URLs. After disabling the setting below, the Catalog URL Rewrites must be reindexed.
• System > Configuration > CATALOG: Catalog > Search Engine Optimizations > Use Categories Path for Product URLs
Real examples? Once upon a time, we managed to lower the number of table records from 27 million to mere 45 thousand (this is terrific 600 times less). The table has remained on the similar size level until now.
Magento is also known to keep excessive amount of data in DB that is related to each store view. The same applies to URLs in the 'core_url_rewrite' table. This is why we built an experimental, yet well-tested feature to rectify the situation:
• System > Configuration > SafeMage: URL Rewrites Optimization > Make URLs for Categories and Products Global: Enable Global URL Keys
It is capable of reducing the size of the "core_url_rewrite" table N times (where N is a number of active store views).
Be aware this is an experimental feature suitable not for every store. Its core is ability to transform URL Keys for categories and products to Global ones, i.e. URL Path of products and categories for all store views would become identical (note this doesn't apply to store code in URL).
Existing records with store_id > 0 get removed and new records with store_id = 0 get added from/to the "core_url_rewrite" table respectively. Note, if you run a one store view shop, the setting won't work for you.
- Optimization procedure guarantees minimum possible size of 'core_url_rewrite' (up to 10000 times less records)
- Fixes various Magento bugs that surface during Catalog URL Rewrites reindex or when saving product / category
- Fixes an issue with reindex when categories are omitted in product URLs:
System > Configuration > CATALOG: Catalog > Search Engine Optimizations > Use Categories Path for Product URLs = NO
- Keeps the 'core_url_rewrite' table on the same size level
- URLs of 'Disabled' or 'Not Visible Individually' products / inactive categories / disabled store views will not end up in 'core_url_rewrite'
- Safe clearing of the table enables removal of excess redirects (with / without set qty of latest redirects to keep or keeping visited URLs from "log_url_info" table)
- Enables unique URL Key verification
- Restores URLs ending with a digit
- Includes schedule clearing
- Checks and clears log tables
CompatibilityMagento CE 1.4.x, 1.5.x, 1.6.x, 1.7.x, 1.8.x, 1.9.x
Magento 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.9.1 (July 10, 2018)
- Addressed an issue with 'Use Categories Path for Product URLs' set to 'NO';
- Limited 'Unique URL Key Verification' to products only.
- v.1.9.0 (June 21, 2018)
- Enable Routing of Products with Category Path in URL.
- v.1.8.1 (January 31, 2018)
- Addressed a cron-related issue.
- v.1.8.0 (November 1, 2017)
- Added a new 'Routing of Pages Ending with a Digit' setting.
- v.1.7.4 (September 25, 2017)
- Improved optimization results when the ‘Use Categories Path for Product URLs’ setting is set to ‘No’.
- v.1.7.3 (August 14, 2017)
- Added compatibility with Mageworx SEO Suite.
- v.1.7.2 (July 14, 2017)
- Addressed an issue with rewrites for disabled categories.
- v.1.7.1 (May 13, 2017)
- Improved reindex process.
- v.1.7.0 (Jan 27, 2017)
- Added Global URL feature (experimental).
- v.1.6.0 (Jan 24, 2017)
- Fixed a Magento issue with reindex when the following setting is set to No: System > Configuration > CATALOG > Catalog > Search Engine Optimizations > Use Categories Path for Product URLs;
- Addressed an issue with categories reindex;
- Addressed minor compatibility issue with Magento CE 1.6.x.
- v.1.5.0 (Jan 16, 2017)
- Added ability to omit disabled stores.
- v.1.4.0 (Dec 16, 2016)
- 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.