If you have a very large Magento catalog, and make regular changes to categories and product categorisation you may find over time that your core_url_rewrite table becomes very, very large.
Some core_url_rewrite tables can exceed 10GB!
Recently I was called upon to take a look at the rewrite table for a Magento 1.9 website, they had 10,000 products and had been operating for a few years. For some reason, out of the blue the website could no longer re-index. Every time they tried it crashed the server.
The reason for this was that their core_url_rewrite table was over 9GB in size - over 45 million rows!
Magento uses the core_url_rewrite table mainly to associate URL keys and the product IDs, as well as any categories and pages. Not only this, but Magento keeps track of any changes that have occurred to the URL key and the changing of categories (for instance moving one product from a category to another) - Magento stores all the old information and automatically points it to the new destination.
The problem with this is that over time it can become very large indeed. When you have 10,000 products, you may have over 2000 indexed in a single category, if you change the category url, or drag it into another category - there is a knock-on effect for the url_rewrite of any child categories - this is why sometimes it takes a very long time to move categories about within Magento.
I'm not sure how much tinkering was involved in the 45 million rows that appeared in this database but it certainly was a lot!
To remedy this, there's only one true clean way to do it - and that is to empty the core_url_rewrite table (in MySQL terms - TRUNCATE).
Make sure that you backup your data first though, because if the re-index doesn't happen for any reason you'd ideally want to re-upload your backed-up core_url_rewrite table in order to get your products and pages loading correctly again.
In my case though, this worked flawlessly, simply truncating the core_url_rewrite table (TRUNCATE core_url_rewrite;) and then re-indexing via admin saved the day. All live URLs worked on the website, there were no missing pages and the table size reduced from 9GB to 40mb - that's a saving of 99.5%. Not only did it work but the website was much faster as a result.
Are there any drawbacks to truncating the core_url_rewrite table?
Yes, when you truncate the table Magento will simply re-populate with the "as of now" correct URLs on the store, which means that any hard-coded URLs, external links or any other possible link to a "deprecated" URL will no longer redirect. The result is that you are probably going to see a lot more 404's in your Google Search Console.
So this is quite a big issue, but you should be able to manually redirect any high-level important URLs back to the appropriate LIVE url manually, as well as make sure you have a very informative and "easy to navigate away from" 404 page set up within Magento.