Too many URL Rewrites

Question

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?

Answer 1

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.

Answer 2

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;