It’s a matter of common knowledge that with time the "core_url_rewrite" Magento 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. It is known that Magento’s code renames equal URL’s into [url_key]-1, [url_key]-2, and so on.
However, next time one saves a product or runs "Catalog URL Rewrites" reindex, for no explicit reason, previously generated URL’s change again:
[url_key]-1 -> [url_key]-3 -> [url_key]-5
[url_key]-2 -> [url_key]-4 -> [url_key]-6
Additionally, for the previous URL to work, Magento’s code adds several redirect records:
[url_key]-1 -> [url_key]-5
[url_key]-3 -> [url_key]-5
[url_key]-2 -> [url_key]-6
[url_key]-4 -> [url_key]-6
Even more, if a product is contained by several categories, a number of the records is multiplied by a number of the categories. This is pure geometric series!
This happens infinite number of times with every reindex or product/category edit. The table grows bigger and bigger, reindexing slows down, and a number of records increases by almost half.
How it works
1) First, admin should empty excess redirects
• System > Configuration > SafeMage > URL Rewrites Optimization > Clearing Settings of ''core_url_rewrite'' Table
The options here are as follows: "Remove All Excess Redirects" or "Qty of Latest Redirects to Keep". The latter option enables admin to set a number of Latest Redirects. It is important if a product/category is represent by an old URL in Google search index, for example [product_url_key]-197123. Thus, a customer entering the website at this URL, after being redirected, would see the initial product page, not 404 error page. In about a month, admin may initiate a total clean-up.
2) After safe clearing has been done, it is recommended to run reindex
• System > Index Management > Catalog URL Rewrites > Reindex Data
URL's of "Disabled" or "Not Visible Individually" products or inactive categories 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.
3) Later on, the extension 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) Optionally admin can enable "Unique URL Key Verification" => Yes.
As a matter of fact, having optimized one of the Magento-based webstore, we’ve decreased the number of table records from 27 million to just 45 thousand (600 times lighter table). The size of that table has remained on the similar level to this day.
- Optimization after effect is guaranteed minimal size of the table "core_url_rewrite" (in some cases 10000 times less records)
- Fixes several bugs of Magento itself appearing during Catalog URL Rewrites reindex or when saving product / category
- Fixes an issue with reindex when the following setting is set to No:
• System > Configuration > CATALOG > Catalog > Search Engine Optimizations > Use Categories Path for Product URLs
- "core_url_rewrite" table remains on the same size level
- URL's of "Disabled" or "Not Visible Individually" products or inactive categories will not be added to "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)
- Unique URL Key Verification while editing products / categories
- Restore URLs ending with a digit
- Clearing by cron schedule
- Log tables checking and clearing
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.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 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.