2

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?

Jochem Gruter
  • 123
  • 4

1 Answers1

3

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

Community
  • 1
BlueC
  • 766
  • 5
  • 16
  • When I truncate the core_url_rewrite table and then re-index, how does magento know the old URLs? Or does it means that I lost all old product URLs and category URL`s? – Jochem Gruter Sep 07 '16 at 06:57
  • Yes if you truncate then you'll lose any old product/category URLs that have been redirected to new ones. Instead of truncating you could try to remove only the erroneous entries from the table: I vaguely recall seeing some SQL to that effect but I can't lay my hands on it right now. – BlueC Sep 07 '16 at 13:08
  • Have added note to answer about truncating table, alternatives queries to remove noisy data, and reference. – BlueC Sep 07 '16 at 13:20