Core_url_rewrites bloated and slow Magento 1.7

[Jakkara]

We are having performance issues with our Magento store. This definetly has something to do with bloated core_url_rewrites db table. This table used to have over 3 000 000 rows, but we decided to truncate that table and do a reindex. After that the store was lightning fast for a while, but now that db table has grown back to 1 000 000 rows and store is slow again.

CPU and memory usage seems to be very high.

How should we fix this?

[chiefair · Accepted solution]

Magento core_url_rewrite table excessively large

[senthilms]

How many sku's / categories / stores and websites you are running with ?

Are you frequently modifying your product names ?

Is there any custom code to make change in the url generation , for example these days people wants to append the sku's in the url.

[magesolutions · Accepted solution]

Let 's try to do these steps

Step 1:  Adjust code

app/code/core/Mage/Catalog/Model/Url.php

Around line 807

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

[Jakkara]

Growth of core_url_rewrites is now under control. Thank you all!

[kalyanchakri]

This module might help you https://github.com/vladsmirnov/url-rewrites. I have used it in one of my projects. It helped in reducing the duplicates while reindexing. I am not sure if this works for 1.7 but I believe there won't be much difference in this. Give it a try.

Please run the shell script in the module to remove the duplicates in your current instance.

As always it is suggested, please take a backup of your database and install the module.