As you can see in the attached image, foreach product in my store I have too many rewrite rules.
I would like to have only one rule with the following syntax: mydomain.com/product-category/product-name-sku.html
How can I clear all the rules and make this new one?
You can truncate the core_url_rewrite table in the database and run the URL Rewrite Index which will rebuild all the URLS. Keep in mind that any custom rewrite you might have added will be gone.
is_system column or check for category or product id being set is_system value 0 items, those are custom It is not a good idea to truncate the core_url_rewrite table if you have custom rewrites and different urls for one category or product in different store views/languages. As written before.
is_system is also not an indicator for deletable items.
What I do:
Create a table core_url_rewrite_plain and select into this table all rewrites you need. Than rename and start reindexer.
The main thing is the WHERE statement :)
SQL for find all relevant urls:
CREATE TABLE core_url_rewrite_plain ( url_rewrite_id int( 10 ) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Rewrite Id', store_id smallint( 5 ) unsigned NOT NULL DEFAULT '0' COMMENT 'Store Id', category_id int( 10 ) unsigned DEFAULT NULL COMMENT 'Category Id', product_id int( 10 ) unsigned DEFAULT NULL COMMENT 'Product Id', id_path varchar( 255 ) DEFAULT NULL COMMENT 'Id Path', request_path varchar( 255 ) DEFAULT NULL COMMENT 'Request Path', target_path varchar( 255 ) DEFAULT NULL COMMENT 'Target Path', is_system smallint( 5 ) unsigned DEFAULT '1' COMMENT 'Defines is Rewrite System', options varchar( 255 ) DEFAULT NULL COMMENT 'Options', description varchar( 255 ) DEFAULT NULL COMMENT 'Deascription', PRIMARY KEY ( `url_rewrite_id` ) , UNIQUE KEY `UNQ_CORE_URL_REWRITE_REQUEST_PATH_STORE_ID` ( `request_path` , `store_id` ) , UNIQUE KEY `UNQ_CORE_URL_REWRITE_ID_PATH_IS_SYSTEM_STORE_ID` ( `id_path` , `is_system` , `store_id` ) , KEY `IDX_CORE_URL_REWRITE_TARGET_PATH_STORE_ID` ( `target_path` , `store_id` ) , KEY `IDX_CORE_URL_REWRITE_ID_PATH` ( `id_path` ) , KEY `IDX_CORE_URL_REWRITE_STORE_ID` ( `store_id` ) , KEY `FK_CORE_URL_REWRITE_PRODUCT_ID_CATALOG_PRODUCT_ENTITY_ENTITY_ID` ( `product_id` ) , KEY `FK_CORE_URL_REWRITE_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID` ( `category_id` ) , KEY `IDX_CATEGORY_REWRITE` ( `category_id` , `is_system` , `product_id` , `store_id` , `id_path` ) ) ENGINE = InnoDB DEFAULT CHARSET = utf8 COMMENT = 'Url Rewrites' AUTO_INCREMENT = 1 ; INSERT INTO core_url_rewrite_plain ( url_rewrite_id, store_id, category_id, product_id, id_path, request_path, target_path, is_system, options, description ) SELECT NULL, curr.store_id, curr.category_id, curr.product_id, curr.id_path, curr.request_path, curr.target_path, curr.is_system, curr.options, curr.description FROM core_url_rewrite_reindex curr WHERE (curr.is_system = 0 AND (curr.options != 'RP' OR curr.options IS NULL)) OR (curr.category_id IS NULL AND curr.product_id IS NULL) ; DROP TABLE IF EXISTS core_url_rewrite; RENAME TABLE core_url_rewrite_plain TO core_url_rewrite; 