Magento – core_url_rewrite table

url-rewrite

When I change the position of products in a category, then the whole webshop will have database problems. When I debugged the problem, I saw that the table core_url_rewrite was the problem. This table has about 843.000 rows. The table has lots of product URL`s, like:

samsung-led-tv-playstation-2704 -> samsung-led-tv-playstation-2707
samsung-led-tv-xbox-one-4506 -> samsung-led-tv-xbox-one-4509
lg-led-tv-xbox-one-2269 -> lg-led-tv-xbox-one-2270
hp-desktop-pc-met-20-inch-scherm-3448 -> hp-desktop-pc-met-23-inch-scherm-7464

As you can see, the URL`s are the same but the ending number is different.

So, what is this number and what causes that those rows were added?

Best Answer

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

Related Solutions

Magento1 Core URL Rewrite – Fix Excessively Large Table

I've managed to stabalize the issue as follows:

Step 1: Rewrite the Catalog URL model (Using your own module: How To)

Note: If you overwrite the core file without using a rewrite this will render your instance of Magento incapable of future upgrades.

As per Jahnni's solution on ~~the MagentoCommerce boards~~ (no longer active with new board), app/code/core/Mage/Catalog/Model/Url.php [ around line 807 Mage_Catalog_Model_Url::getProductRequestPath() ]

From:

if ($product->getUrlKey() == '' && !empty($requestPath)
   && strpos($existingRequestPath, $requestPath) === 0
)

To:

if (!empty($requestPath)
           && strpos($existingRequestPath, $requestPath) === 0
)

Step 2: Truncate

Truncate the core_url_rewrite table

Step 3: Reindex & Flush Caches

Initiate the re-indexing process on Core URL Rewrites. Thereafter, you'll want to flush the Magento cache & storage cache.

System → Cache Management → Flush Magento Cache

System → Cache Management → Flush Cache Storage

Voila, you're all set. You'll notice if you re-run the indexer, the table should stay constant in size (unless you've added more products inbetween or if you have duplicate category names).

Magento – Why is the url added to the enterprise_url_rewrite table but not core_url_rewrite

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.

Best Answer

Related Solutions

Magento1 Core URL Rewrite – Fix Excessively Large Table

Magento – Why is the url added to the enterprise_url_rewrite table but not core_url_rewrite

Related Topic