how to reduce the size of core_url_rewrite: Magento 1.9

Question

I have a lot of entries in the table core_url_rewrite, how can I reduce ? I'm using magento 1.9.x Actually the table has ~3,680,554 records and 2.5 GB of size. I put in the pas about 100 redirect (manually), I cant clear it, just to delete the unnecessary records.

regards

Answer 1

You can truncate core_url_rewrite and run re-indexing to regenerate URLs.

First execute this on Database:

TRUNCATE core_url_rewrite;

then execute this on shell to re-index url rewrites table:

php -f ./shell/indexer.php --reindex catalog_url

Note:
There is a problem with this criteria.

• All the additional URL rewrites that you had manually generated would be lost
• All the redirects from old URL to new URL would gone to 404 (Not good for SEO) either related to category or product

I would suggest you first take backup of your code before doing this.

Hope it will resolve your issue.