8

Our core_url_rewrite table seems to be growing excessively (21M rows currently) - I know there have been other questions about this, but none of them seem to mention this particular oddity: a lot of the new rows being added have is_system = 0, and the id_path is something like "97704000_1422557940". The number after the underscore looks to be the timestamp that the row was added, but I'm not sure what the first number is.

The advice for core_url_rewrite problems always seems to be to truncate the table and re-index, and it may come to that, but we have a lot of custom rewrites in the table so having to constantly re-add them is going to be a real pain, and I'd much rather get to the root of the issue.

We've just upgraded to 1.9.1.0, but there are rows in table going back nearly two years (!).

Any ideas?

6

This is a Classic issue with rewrites. The root cause is not having unique URL keys. Typically caused by having simple products part of configurable with the same name.

For obvious reasons, one request path (URL) must match one action in Magento. Therefore all request paths need to be unique. Product and category URL paths are created from their URL keys and typically when you have configurable products, store owners / backend workers don't take the time to ensure the simple products below a configurable have different URL keys. This causes Magento to insert a dash and a sequence number. Given a configurable product with 4 simples this means at least 4 URLs with a sequence are added each iteration (because Magento doesn't / can't distinguish between runs that a sequence is already created). This adds up quickly in a big catalog.

The workflow to recover is as follows:

  1. Ensure all URL keys are unique fixing your input and do another reindex of rewrites.
  2. Remove all rewrites that match WHERE id_path LIKE "%_%" AND options="RP" AND (catalog_id IS NOT NULL OR product_id IS NOT NULL) AND target_path NOT IN (temp_table).
  3. For the remaining rewrites matching WHERE id_path LIKE "%_%" AND options="RP" AND (catalog_id IS NOT NULL OR product_id IS NOT NULL) set the request_path to the target_path and the set the target_path to the request_path that matches the category_id-product_id combination and where options IS NULL.
  4. Install this extension and enable all optimizations
  5. Reindex rewrites at least twice,verifying the row count is consistent (given no changes to products or categories).
  6. Monitor Webmaster Tools and 404's for additional stale URLs that are still in spiders and should be redirected. Preferably implement the 301 in your webserver to keep core_url_rewrite clean.

Notes: This script helps with creating URL keys that are unique, by iterating attribute values and appending them till a unique key is generated. Note that this script does not check conflicts between a category and a product. Typically this is not a problem, since categories are naturally named in plural, but if you sell for example sheep or fish this can still be a problem. Another corner case is a clash between catalog URLs and CMS pages. This script doesn't check it, but it also doesn't impact the rewrites as the CMS page identifiers are not in there. This will simply result in either the CMS page or the category/product page being shown where one would expect to see the other.

The temp_table mentioned should be filled with URLs that are in all sitemaps. This mitigates some of the SEO impact by keeping the current variant of dash and sequence number alive and in step 3 this is then rewritten to the correct URL. The extension in step 4 prevents a number of URLs from entering the core_url_rewrite table, most notably products that are not set to "catalog/search" visibility. When you have simple products that are part of a configurable and not listed separately, these should be marked "not visible individually" and this extension then prevents them from entering rewrites. This is a valuable optimization for stores with configurable products regardless of them having this URL rewrite problem. Regarding step 5, if no changes are done to the url keys of products and categories, then every indexation shall generate the same amount of rewrites. If this is not the case, you still have a conflict somewhere and should hunt it down.

Hope this clarifies things a bit.

  • nice answer and +1 for that. But it would be nice if you add more details such as how to tackle this core issue, any links erc. – Rajeev K Tomy May 8 '15 at 6:50
  • Will do. Was on the way out. – Melvyn May 8 '15 at 6:51
0

I believe these are typically programmatically generated redirects when you change products and categories in the catalog. They are meant to maintain old links to send customers to the new locations, but you can probably purge them after a while since they do tend to build up over time—especially if you have multiple websites/stores/views and lots of products.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.