Magento URL indexing and core_url_rewrite table

Question

Can anybody shed some light on how Magento determine whether to create a new URL rewrite for a product? Every time I run the Catalog URL Rewrite reindex process the number of rows in core_url_rewrite increases by roughly 10,000 rows. Since no product data has been modified in the mean time, why is a new URL generated?

Ephemerality

I am having the same problem and so far I suspect some inconsistent data. I will follow this topic ... may be we will find the answer here . Do you use some scripts, that automatically sync the prices, quantities and etc? – Hamann

Yes, we use urapidflow to import information generated from a remote server. However, this shouldn't be too relevant as the extra rows happen even if I disabled all scripts and just run reindex. I truncated the table and clicked the reindex 3 times on my local machine and each time it happened. – Leon

How many stores do you have? – Hamann

Only the one. i've had a bit of a look into the code, it seems like some difference was introduced in our upgrade from 1.5.1 to 1.7 that means it always generates a new value rather than using a new one. – Leon

Answer 1

This problem still exists in Magento 1.7, but I have no clue about 1.8 or 1.9. Magento does not delete old entries it seems. This can slow down your database seriously. The easiest way to solve this is to truncate the table and to reindex it.

• Login to mysql and select (use) the proper database
• Execute the following query: truncate table core_url_rewrite;
• Goto the shell folder in your magento root (commandline) and execute the following command: php -f indexer.php -- -reindex catalog_url
• Alternatively you can do this in the admin: System > Index Management; Reindex Catalog URL Rewrites

Then check in the admin: Catalog > URL Rewrite Management, and see if the new entries have been created. If this shows no items, URL rewrites won't work!

Patellate

Answer 2

You can run the following SQL to find out which URLs belong to products that do not exist:

SELECT `cur`.`product_id`
FROM `core_url_rewrite` AS `cur`
LEFT OUTER JOIN `catalog_product_entity` AS `cpe`
ON `cur`.`product_id` = `cpe`.`entity_id`
WHERE `cpe`.`entity_id` IS NULL
AND `cur`.`product_id` IS NOT NULL

... you should see a list of entity_ids that do not exist in

`catalog_product_entity`

EcomDev have a free URL rewrite module (http://shop.ecomdev.org/url-rewrite-indexer.html) that I've found useful.

EDIT

To answer the question, new URLs are generated because when URL re-indexing is fired by:

Mage_Catalog_Model_Indexer_Url::reindexAll()

... which is a wrapper around:

Mage_Catalog_Model_Url::refreshRewrites

... no check is made if data has been changed. If that's something you require, you could override that class and re-write the functionality to compare differences.

Hardboiled

Hi james, thanks for the reply. I don't believe the problem has anything to do with products that don't exist, I believe it's the generation of a new URLs for ones that do exists, every time the index is ran, that is causing the issue. Since I truncated the core_url_rewrites table in my dev environment before running the test, there shouldn't be any for products that don't exist. I've come across the EcomDev extension before and may try it in future, but for now I'd like to get to the bottom of why the core one creates a new URL. – Leon

Hi Cags, I've edited my answer which is hopefully more useful. Any questions, feel free to ask. – Hardboiled

Hi james, sure I understand that it's those pieces of code that get's called. But from my rough tests the same number of URLs doesn't seem to be generated in version 1.5 of Magento. Let's assume that, as you state, nowhere in the stack is there a check as to whether a value has changed (which upon a cursory glance seems to be the case). Can you think of a single good reason to give every product on your site a new URL every-time an index process is ran? It sounds madness to me, especially if it's a new 'feature' (I need to run more tests). – Leon

Re-index all does as it implies and doesn't check before it's run: it's the same with each of the other core indexers (CatalogSearch, CatalogInventory etc). It's been a while since I've used 1.5 so I don't know how old the feature is. Maybe you could submit a bug or feature request for Magento to include 'check on re-index all' (or write a patch?) – Hardboiled

I did a quick search of the bug-tracker and came up with this magentocommerce.com/bug-tracking/issue/?issue=13662 , it seems I'm not the only person to be suffering from this. Seems it might only happen when you have products with the same name (of which we have lots due to the way they are imported, simples aren't visible individually, but all have the same name). – Leon

Answer 3

I'm using Magento 1.8 and my core_url_rewrite table had about 5 Millions!!!! records. Finally i've overwritten the function beforeSave in Mage_Catalog_Model_Product_Attribute_Backend_Urlkey and generate my own url key out of the first five words of the productname. If the first 5 words aren't unique, i append an some digits. If someone is interested in my code, i can post some snippets.

after overwriting Mage_Catalog_Model_Product_Attribute_Backend_Urlkey i truncated core_url_rewrite und rebuilt the index. finally i got about 60.000 URLs...and reindex process doesnt add new ones.

but you have to keep in mind, that custom url rewrites are getting lost, when truncating and rebuiling core_url_rewrite table. And maybe you have some unexpected problems with your head of seo. cause all 302er redirects are getting lost

cheers

Gynecologist

Answer 4

This issue occurred due to a known bug in the Product and Category Indexing system in earlier versions of both Magento Enterprise Edition and Community Edition.

The full explanation of the root cause analysis and additional confirmation of the research and solution provided for both Category and Product URLs can be found at Magento core_url_rewrite table excessively large

Tamayo