Magento1 Core URL Rewrite – Fix Excessively Large Table

In Magento Enterprise the is a new module for storing url rewrite Enterprise_UrlRewrite. This module contains new tables for storing the rewrites, some layout files for the admin section, a new admin html controller and a new url matcher to name but a few items.

To understand when the enterprise system uses the new tables for saving rewrites we can first look into the controller Enterprise_UrlRewrite_Adminhtml_UrlrewriteController, here we find the function _getRedirect which is used to to load the current redirect being worked on and returns a model of type Enterprise_UrlRewrite_Model_Redirect.

If we then look into the config.xml for Enterprise_UrlRewrite you can see that it has it's own table information stored so that when the model is being used it does not use the standard community tables.

<models>
    <enterprise_urlrewrite>
        <class>Enterprise_UrlRewrite_Model</class>
        <resourceModel>enterprise_urlrewrite_resource</resourceModel>
    </enterprise_urlrewrite>
    <enterprise_urlrewrite_resource>
        <class>Enterprise_UrlRewrite_Model_Resource</class>
        <entities>
            <url_rewrite>
                <table>enterprise_url_rewrite</table>
            </url_rewrite>
            <redirect>
                <table>enterprise_url_rewrite_redirect</table>
            </redirect>
            <redirect_rewrite>
                <table>enterprise_url_rewrite_redirect_rewrite</table>
            </redirect_rewrite>
        </entities>
    </enterprise_urlrewrite_resource>
</models>

The structure of these new tables, is in my opinion vast improvement of the community editions single table, as the category and product information is moved into separate tables and also the redirect information is also separated.

This is a long-standing problem with Magento 1.x creating spurious duplicate rewrites in the table. As you've found, the table size can become extremely large.

There are various suggestions on how to fix it but I have repeatedly found this is the best solution: https://gist.github.com/edannenberg/5310008

Naturally you should apply this patch against a local copy of Url.php so as not to change any core files. Even better, create a small extension that uses a rewrite to extend and modify the class.

Once the fix is applied, truncate the core_url_rewrite table and then re-index.

Note: Truncating the table will remove valid redirects that were created by Magento, for example when a URL key was updated. Re-indexing won't recreate these. If this is a problem then an alternative to truncating the table is to try to remove only the noisy data from the table.

How you do that will depend on your URL patterns and I would recommend you thoroughly understand and test your RegEx before trying these examples. Also, make a backup of the table first and/or try this on dev/staging environment first!

DELETE FROM `core_url_rewrite` WHERE `request_path` REGEXP '\\-[0-9]{1,5}$';

Or perhaps more accurate (only works if you use .html extensions on your paths):

DELETE FROM core_url_rewrite WHERE is_system <> 1 AND id_path REGEXP "^[0-9]+_[0-9]+$" AND (request_path REGEXP ".*-[0-9]*\.html" OR target_path = request_path);

Ref: Magento core_url_rewrite table excessively large