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:
- Ensure all URL keys are unique fixing your input and do another reindex of rewrites.
- 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).
- 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.
- Install this extension and enable all optimizations
- Reindex rewrites at least twice,verifying the row count is consistent (given no changes to products or categories).
- 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.
