100

I've noticed a large amount of reports that this table itself can become extremely cluttered, I'm running a site with ~5000 SKUs and ~250 categories (single-store) and a resultant core_url_rewrite table of over 600,000 lines and over 500MB large which is insane.

This can slow down site performance and result in a very bulky database. I've done some digging and found quite a few posts regarding this, most notably:

//These links have been removed since the implementation of the new boards

Now I understand that the table can be truncated and reindexed, but this doesn't solve the problem, it just prolongs the issue from happening again.

From what I understand, part of the issue is products which have the same url key based on the product's name, thus resulting in indexed links.

A fix mentioned is:

app/code/core/Mage/Catalog/Model/Url.php on line ~807:

Change:

 if ($product->getUrlKey() == '' && !empty($requestPath)
       && strpos($existingRequestPath, $requestPath) === 0
 ) 

To:

 if (!empty($requestPath)
       && strpos($existingRequestPath, $requestPath) === 0
 ) 

But even this does not completely resolve the issue.

My question is as follows:

If you have experienced this issue, have you managed to set up an effective, logical & efficient algorithm which does not involve "managing" the issue repeatedly, but actually resolving the matter once and for all?

Would really appreciate some insight into this.

BTW: Please do yourself a favor and check what your table is looking like right now, you may be experiencing this issue and the performance impact as a result thereof without even knowing it - I didn't.

Edit: I have been in contact with www.Nexcess.net (a Magento platinum hosting partner) and they have confirmed that they have had clients request that their core_url_rewrite table requires truncation as a result of being too bulky.

A big worry of mine is the SEO impact that this may have, which is why I'd like a solution as opposed to procrastinating the issue from arising again.

Update: Nexcess mentioned that with the duplicate products within the table it may actually be hurting SEO as it is.

  • Wow, that's a staggeringly large table. I checked my own (200 products) and it only has ~800 rows, but we don't have a problem duplicating product name/URLs. As a reference point, we have about 6.6 rows per visible product. I'll admit this not a terrible realistic comparison, but at that rate, with 5,000 products we'd only have ~30,000 rows or so. I can well understand your need for a solution, and will watch this question as I'm about to implement a much bigger site. – Pete855217 Mar 29 '14 at 9:23
  • @Pete855217: this question sounds you interesting even though you had not upvoted it. – Mohammad Faisal Mar 29 '14 at 11:00
  • 1
    There was a bug in EE1.12 which cause re-writes to be re-created on each save. It is possible that your version of 1.7 has this same bug. As I recall the patch for 1.12 also worked on 1.7 – brentwpeterson Mar 29 '14 at 13:53
  • 1
    Very helpful article! We have 130,000 active products, and 25,000 disabled products, our core_url_rewrite_table has 2744023 records in it..... so this week we are getting to work remedying this!! This article looks to be a good starting point. – MagentoMac Jul 28 '14 at 20:36
  • Edited the post to include how not to delete your custom rewrites in Magento. – espradley May 15 '15 at 15:09

13 Answers 13

75

I've managed to stabalize the issue as follows:

Step 1: Rewrite the Catalog URL model (Using your own module: How To)

Note: If you overwrite the core file without using a rewrite this will render your instance of Magento incapable of future upgrades.

As per Jahnni's solution on the MagentoCommerce boards (no longer active with new board), app/code/core/Mage/Catalog/Model/Url.php [ around line 807 Mage_Catalog_Model_Url::getProductRequestPath() ]

From:

if ($product->getUrlKey() == '' && !empty($requestPath)
   && strpos($existingRequestPath, $requestPath) === 0
) 

To:

if (!empty($requestPath)
           && strpos($existingRequestPath, $requestPath) === 0
) 

Step 2: Truncate

Truncate the core_url_rewrite table

Step 3: Reindex & Flush Caches

Initiate the re-indexing process on Core URL Rewrites. Thereafter, you'll want to flush the Magento cache & storage cache.

SystemCache ManagementFlush Magento Cache

SystemCache ManagementFlush Cache Storage

Voila, you're all set. You'll notice if you re-run the indexer, the table should stay constant in size (unless you've added more products inbetween or if you have duplicate category names).

  • 5
    Great, my core_url_rewrite table was 3.2 GB now is 36.8 MB :D by muppet – Fabian Blechschmidt Jul 24 '14 at 10:40
  • I have a similar problem. Magento URL rewrite append random number in Url. Please look at the screenshot attached from google web master tools. As you can see the product "Beige Embroidered Wedding Saree" has nine different URL's but the it is only one product and pointing to only one URL ending with 878. The actual URL key doesn't have a random number in the end(screenshot attached). My store is fairly a new one and the size of core_url_rewrite is not that big. So I am not sure if I should go ahead and do Step 1 & 2 or Only Step 1. If I perform step 2 I will lose my custom rewrites. – Zoya May 25 '15 at 9:28
  • I am running 1.9.1 and here are missed screenshot urls. monosnap.com/image/duL0f64WWlACtlt9kcn04BWqY3L5Xl monosnap.com/image/osFk8kYNAr00XLdFTGTIOaydaW5yqS – Zoya May 25 '15 at 9:31
  • 2
    I would export the existing table first. Then I would proceed with steps 1, 2 and 3. Take a look at core_url_rewrite table now and note down the number of records. Run step 3 again (the re-indexing), and refresh your view on the core_url_rewrite table. If the number is the same, you've resolved successfully. Then go ahead and manually merge your custom rewrites. All the best. – Moose May 25 '15 at 9:39
  • 2
    This fix only works for products, not for categories with identical URL keys. See @Simon s' answer for a better solution (with the patch file) – Giel Berkers Jun 25 '15 at 12:22
43

While I hope someone here comes up with an answer, I don't know that you'll find one. This table gets bulky for a lot of different reasons. Bugs in earlier (and possibly current) versions of Magento is one. Another is there's logic in this table that tries to track changes to the URL key value so that 301/302 rewrites are setup for old products. Because of this, and complicating things, truncating the table and regenerating may make existing URL rewrites go away, and this will have an unknown effect on your search engine listing (not necessity bad, just hard to predict).

My general advice to clients who ask is

  1. Leave the giant growing table as is if you don't have a good handle on your URL/SEO situation

  2. Until the table size starts being a problem (generating site maps, for example). When that happens, get a handle on your URL/SEO situation.

  3. Once you have a handle on your URL/SEO situation, backup the table, then truncate the table and regenerate. Address any URL/SEO problems caused by the truncating.

  4. Automate step 3

Trying to fix this on the Magento code level is admirable, but you'll be swimming upstream. Sometimes it's better to accept that "That's just Magento being Magento", and to solve the problem with and external process.

  • thanks for the advice, it's a pity about the situation but I do think that it is going to have to be handled by an external process as you mentioned, urgh. – Moose Mar 31 '14 at 5:40
  • 2
    This giant table can already cause SEO problems, as the canonical for a given product will change constantly. If you have a seperate storeview for mobile and desktop, then it's even worse as their URLs will differ. – Melvyn Mar 28 '15 at 17:48
  • A bit disappointing answer to me ... – Fra Mar 16 '17 at 8:13
  • @Alan Storm, how do you feel about the answer posted by Moose after you posted this answer? Do you see the same risks? – Goose Feb 9 '18 at 12:55
24

I would like to add a fix for this url rewrite indexer bug which has been developed at the bugathon in March 2013 and which has been further improved afterwards. It should solve this issue. As a reference, here is the patch file from the link:

diff -rupN mage_org/app/code/core/Mage/Catalog/Model/Url.php src_shop/app/code/core/Mage/Catalog/Model/Url.php
--- mage_org/app/code/core/Mage/Catalog/Model/Url.php   2013-11-19 00:48:25.679009391 +0100
+++ src_shop/app/code/core/Mage/Catalog/Model/Url.php   2013-11-19 00:49:24.188005601 +0100
@@ -643,13 +643,24 @@ class Mage_Catalog_Model_Url
                 $this->_rewrite = $rewrite;
                 return $requestPath;
             }
+
+            // avoid unnecessary creation of new url_keys for duplicate url keys
+            $noSuffixPath = substr($requestPath, 0, -(strlen($suffix)));
+            $regEx = '#^('.preg_quote($noSuffixPath).')(-([0-9]+))?('.preg_quote($suffix).')#i';
+            $currentRewrite = $this->getResource()->getRewriteByIdPath($idPath, $storeId);
+            if ($currentRewrite && preg_match($regEx, $currentRewrite->getRequestPath(), $match)) {
+                $this->_rewrite = $currentRewrite;
+                return $currentRewrite->getRequestPath();
+            }
+
             // match request_url abcdef1234(-12)(.html) pattern
             $match = array();
             $regularExpression = '#^([0-9a-z/-]+?)(-([0-9]+))?('.preg_quote($suffix).')?$#i';
             if (!preg_match($regularExpression, $requestPath, $match)) {
                 return $this->getUnusedPath($storeId, '-', $idPath);
             }
-            $match[1] = $match[1] . '-';
+            $match[1] = $noSuffixPath . '-'; // always use full prefix of url_key
+            unset($match[3]); // don't start counting with a possible number in the url_key
             $match[4] = isset($match[4]) ? $match[4] : '';

             $lastRequestPath = $this->getResource()


Additionally, I would like to add the EE patch PATCH_SUPEE-389_EE_1.12.0.2_v2.sh, which is now available on GitHub:

#!/bin/bash
# Patch apllying tool template
# v0.1.2
# (c) Copyright 2013. Magento Inc.
#
# DO NOT CHANGE ANY LINE IN THIS FILE.

# 1. Check required system tools
_check_installed_tools() {
    local missed=""

    until [ -z "$1" ]; do
        type -t $1 >/dev/null 2>/dev/null
        if (( $? != 0 )); then
            missed="$missed $1"
        fi
        shift
    done

    echo $missed
}

REQUIRED_UTILS='sed patch'
MISSED_REQUIRED_TOOLS=`_check_installed_tools $REQUIRED_UTILS`
if (( `echo $MISSED_REQUIRED_TOOLS | wc -w` > 0 ));
then
    echo -e "Error! Some required system tools, that are utilized in this sh script, are not installed:\nTool(s) \"$MISSED_REQUIRED_TOOLS\" is(are) missed, please install it(them)."
    exit 1
fi

# 2. Determine bin path for system tools
CAT_BIN=`which cat`
PATCH_BIN=`which patch`
SED_BIN=`which sed`
PWD_BIN=`which pwd`
BASENAME_BIN=`which basename`

BASE_NAME=`$BASENAME_BIN "$0"`

# 3. Help menu
if [ "$1" = "-?" -o "$1" = "-h" -o "$1" = "--help" ]
then
    $CAT_BIN << EOFH
Usage: sh $BASE_NAME [--help] [-R|--revert] [--list]
Apply embedded patch.

-R, --revert    Revert previously applied embedded patch
--list          Show list of applied patches
--help          Show this help message
EOFH
    exit 0
fi

# 4. Get "revert" flag and "list applied patches" flag
REVERT_FLAG=
SHOW_APPLIED_LIST=0
if [ "$1" = "-R" -o "$1" = "--revert" ]
then
    REVERT_FLAG=-R
fi
if [ "$1" = "--list" ]
then
    SHOW_APPLIED_LIST=1
fi

# 5. File pathes
CURRENT_DIR=`$PWD_BIN`/
APP_ETC_DIR=`echo "$CURRENT_DIR""app/etc/"`
APPLIED_PATCHES_LIST_FILE=`echo "$APP_ETC_DIR""applied.patches.list"`

# 6. Show applied patches list if requested
if [ "$SHOW_APPLIED_LIST" -eq 1 ] ; then
    echo -e "Applied/reverted patches list:"
    if [ -e "$APPLIED_PATCHES_LIST_FILE" ]
    then
        if [ ! -r "$APPLIED_PATCHES_LIST_FILE" ]
        then
            echo "ERROR: \"$APPLIED_PATCHES_LIST_FILE\" must be readable so applied patches list can be shown."
            exit 1
        else
            $SED_BIN -n "/SUP-\|SUPEE-/p" $APPLIED_PATCHES_LIST_FILE
        fi
    else
        echo "<empty>"
    fi
    exit 0
fi

# 7. Check applied patches track file and its directory
_check_files() {
    if [ ! -e "$APP_ETC_DIR" ]
    then
        echo "ERROR: \"$APP_ETC_DIR\" must exist for proper tool work."
        exit 1
    fi

    if [ ! -w "$APP_ETC_DIR" ]
    then
        echo "ERROR: \"$APP_ETC_DIR\" must be writeable for proper tool work."
        exit 1
    fi

    if [ -e "$APPLIED_PATCHES_LIST_FILE" ]
    then
        if [ ! -w "$APPLIED_PATCHES_LIST_FILE" ]
        then
            echo "ERROR: \"$APPLIED_PATCHES_LIST_FILE\" must be writeable for proper tool work."
            exit 1
        fi
    fi
}

_check_files

# 8. Apply/revert patch
# Note: there is no need to check files permissions for files to be patched.
# "patch" tool will not modify any file if there is not enough permissions for all files to be modified.
# Get start points for additional information and patch data
SKIP_LINES=$((`$SED_BIN -n "/^__PATCHFILE_FOLLOWS__$/=" "$CURRENT_DIR""$BASE_NAME"` + 1))
ADDITIONAL_INFO_LINE=$(($SKIP_LINES - 3))p

_apply_revert_patch() {
    DRY_RUN_FLAG=
    if [ "$1" = "dry-run" ]
    then
        DRY_RUN_FLAG=" --dry-run"
        echo "Checking if patch can be applied/reverted successfully..."
    fi
    PATCH_APPLY_REVERT_RESULT=`$SED_BIN -e '1,/^__PATCHFILE_FOLLOWS__$/d' "$CURRENT_DIR""$BASE_NAME" | $PATCH_BIN $DRY_RUN_FLAG $REVERT_FLAG -p0`
    PATCH_APPLY_REVERT_STATUS=$?
    if [ $PATCH_APPLY_REVERT_STATUS -eq 1 ] ; then
        echo -e "ERROR: Patch can't be applied/reverted successfully.\n\n$PATCH_APPLY_REVERT_RESULT"
        exit 1
    fi
    if [ $PATCH_APPLY_REVERT_STATUS -eq 2 ] ; then
        echo -e "ERROR: Patch can't be applied/reverted successfully."
        exit 2
    fi
}

REVERTED_PATCH_MARK=
if [ -n "$REVERT_FLAG" ]
then
    REVERTED_PATCH_MARK=" | REVERTED"
fi

_apply_revert_patch dry-run
_apply_revert_patch

# 9. Track patch applying result
echo "Patch was applied/reverted successfully."
ADDITIONAL_INFO=`$SED_BIN -n ""$ADDITIONAL_INFO_LINE"" "$CURRENT_DIR""$BASE_NAME"`
APPLIED_REVERTED_ON_DATE=`date -u +"%F %T UTC"`
APPLIED_REVERTED_PATCH_INFO=`echo -n "$APPLIED_REVERTED_ON_DATE"" | ""$ADDITIONAL_INFO""$REVERTED_PATCH_MARK"`
echo -e "$APPLIED_REVERTED_PATCH_INFO\n$PATCH_APPLY_REVERT_RESULT\n\n" >> "$APPLIED_PATCHES_LIST_FILE"

exit 0


SUPEE-389 | EE_1.12.0.2 | v1 | 53c8ca52583358953b143aaa1a78cf409e8dd846 | Thu Jun 20 10:36:39 2013 +0300 | v1.12.0.2..HEAD

__PATCHFILE_FOLLOWS__
diff --git app/code/core/Mage/Catalog/Model/Url.php app/code/core/Mage/Catalog/Model/Url.php
index fa55fc5..a755b46 100644
--- app/code/core/Mage/Catalog/Model/Url.php
+++ app/code/core/Mage/Catalog/Model/Url.php
@@ -609,6 +609,23 @@ class Mage_Catalog_Model_Url
      */
     public function getUnusedPath($storeId, $requestPath, $idPath)
     {
+        $urlKey = '';
+        return $this->getUnusedPathByUrlkey($storeId, $requestPath, $idPath, $urlKey);
+    }
+
+    /**
+     * Get requestPath that was not used yet.
+     *
+     * Will try to get unique path by adding -1 -2 etc. between url_key and optional url_suffix
+     *
+     * @param int $storeId
+     * @param string $requestPath
+     * @param string $idPath
+     * @param string $urlKey
+     * @return string
+     */
+    public function getUnusedPathByUrlkey($storeId, $requestPath, $idPath, $urlKey = '')
+    {
         if (strpos($idPath, 'product') !== false) {
             $suffix = $this->getProductUrlSuffix($storeId);
         } else {
@@ -645,21 +662,22 @@ class Mage_Catalog_Model_Url
             }
             // match request_url abcdef1234(-12)(.html) pattern
             $match = array();
-            $regularExpression = '#^([0-9a-z/-]+?)(-([0-9]+))?('.preg_quote($suffix).')?$#i';
+            $regularExpression = '#(?P<prefix>(.*/)?' . preg_quote($urlKey) . ')(-(?P<increment>[0-9]+))?(?P<suffix>'
+                . preg_quote($suffix) . ')?$#i';
             if (!preg_match($regularExpression, $requestPath, $match)) {
-                return $this->getUnusedPath($storeId, '-', $idPath);
+                return $this->getUnusedPathByUrlkey($storeId, '-', $idPath, $urlKey);
             }
-            $match[1] = $match[1] . '-';
-            $match[4] = isset($match[4]) ? $match[4] : '';
+            $match['prefix'] = $match['prefix'] . '-';
+            $match['suffix'] = isset($match['suffix']) ? $match['suffix'] : '';

             $lastRequestPath = $this->getResource()
-                ->getLastUsedRewriteRequestIncrement($match[1], $match[4], $storeId);
+                ->getLastUsedRewriteRequestIncrement($match['prefix'], $match['suffix'], $storeId);
             if ($lastRequestPath) {
-                $match[3] = $lastRequestPath;
+                $match['increment'] = $lastRequestPath;
             }
-            return $match[1]
-                . (isset($match[3]) ? ($match[3]+1) : '1')
-                . $match[4];
+            return $match['prefix']
+                . (isset($match['increment']) ? ($match['increment']+1) : '1')
+                . $match['suffix'];
         }
         else {
             return $requestPath;
@@ -699,7 +717,7 @@ class Mage_Catalog_Model_Url
     {
         $storeId = $category->getStoreId();
         $idPath  = $this->generatePath('id', null, $category);
-        $suffix  = $this->getCategoryUrlSuffix($storeId);
+        $categoryUrlSuffix = $this->getCategoryUrlSuffix($storeId);

         if (isset($this->_rewrites[$idPath])) {
             $this->_rewrite = $this->_rewrites[$idPath];
@@ -713,27 +731,27 @@ class Mage_Catalog_Model_Url
             $urlKey = $this->getCategoryModel()->formatUrlKey($category->getUrlKey());
         }

-        $categoryUrlSuffix = $this->getCategoryUrlSuffix($category->getStoreId());
         if (null === $parentPath) {
             $parentPath = $this->getResource()->getCategoryParentPath($category);
         }
         elseif ($parentPath == '/') {
             $parentPath = '';
         }
-        $parentPath = Mage::helper('catalog/category')->getCategoryUrlPath($parentPath,
-                                                                           true, $category->getStoreId());
+        $parentPath = Mage::helper('catalog/category')->getCategoryUrlPath($parentPath, true, $storeId);

-        $requestPath = $parentPath . $urlKey . $categoryUrlSuffix;
-        if (isset($existingRequestPath) && $existingRequestPath == $requestPath . $suffix) {
+        $requestPath = $parentPath . $urlKey;
+        $regexp = '/^' . preg_quote($requestPath, '/') . '(\-[0-9]+)?' . preg_quote($categoryUrlSuffix, '/') . '$/i';
+        if (isset($existingRequestPath) && preg_match($regexp, $existingRequestPath)) {
             return $existingRequestPath;
         }

-        if ($this->_deleteOldTargetPath($requestPath, $idPath, $storeId)) {
+        $fullPath = $requestPath . $categoryUrlSuffix;
+        if ($this->_deleteOldTargetPath($fullPath, $idPath, $storeId)) {
             return $requestPath;
         }

-        return $this->getUnusedPath($category->getStoreId(), $requestPath,
-                                    $this->generatePath('id', null, $category)
+        return $this->getUnusedPathByUrlkey($storeId, $fullPath,
+            $this->generatePath('id', null, $category), $urlKey
         );
     }

@@ -798,7 +816,8 @@ class Mage_Catalog_Model_Url
             $this->_rewrite = $this->_rewrites[$idPath];
             $existingRequestPath = $this->_rewrites[$idPath]->getRequestPath();

-            if ($existingRequestPath == $requestPath . $suffix) {
+            $regexp = '/^' . preg_quote($requestPath, '/') . '(\-[0-9]+)?' . preg_quote($suffix, '/') . '$/i';
+            if (preg_match($regexp, $existingRequestPath)) {
                 return $existingRequestPath;
             }

@@ -836,7 +855,7 @@ class Mage_Catalog_Model_Url
         /**
          * Use unique path generator
          */
-        return $this->getUnusedPath($storeId, $requestPath.$suffix, $idPath);
+        return $this->getUnusedPathByUrlkey($storeId, $requestPath.$suffix, $idPath, $urlKey);
     }

     /**
@@ -891,8 +910,8 @@ class Mage_Catalog_Model_Url
                 $parentPath = Mage::helper('catalog/category')->getCategoryUrlPath($parentPath,
                     true, $category->getStoreId());

-                return $this->getUnusedPath($category->getStoreId(), $parentPath . $urlKey . $categoryUrlSuffix,
-                    $this->generatePath('id', null, $category)
+                return $this->getUnusedPathByUrlkey($category->getStoreId(), $parentPath . $urlKey . $categoryUrlSuffix,
+                    $this->generatePath('id', null, $category), $urlKey
                 );
             }

@@ -913,14 +932,14 @@ class Mage_Catalog_Model_Url
                 $this->_addCategoryUrlPath($category);
                 $categoryUrl = Mage::helper('catalog/category')->getCategoryUrlPath($category->getUrlPath(),
                     false, $category->getStoreId());
-                return $this->getUnusedPath($category->getStoreId(), $categoryUrl . '/' . $urlKey . $productUrlSuffix,
-                    $this->generatePath('id', $product, $category)
+                return $this->getUnusedPathByUrlkey($category->getStoreId(), $categoryUrl . '/' . $urlKey . $productUrlSuffix,
+                    $this->generatePath('id', $product, $category), $urlKey
                 );
             }

             // for product only
-            return $this->getUnusedPath($category->getStoreId(), $urlKey . $productUrlSuffix,
-                $this->generatePath('id', $product)
+            return $this->getUnusedPathByUrlkey($category->getStoreId(), $urlKey . $productUrlSuffix,
+                $this->generatePath('id', $product), $urlKey
             );
         }

If you want to use this patch with CE, make sure to test it properly, because it has been developed for EE.

  • Have you yourself tested this EE patch on CE? – Tyler V. Mar 19 '15 at 18:14
  • @TylerV. Nope... – Simon Mar 20 '15 at 13:22
  • 3
    I've tried this patch in EE 1.9.1.1 and can conform it works. It fixes the problem with products and categories with identical url keys. Let's hope they implement this in a future release soon. – Giel Berkers Jun 25 '15 at 12:21
  • 1
    Thanks Simon, just went from 1GB to 3MB on one clients website... Had to truncate it before every 6 months, hope it will remain small now:) – willem wigman Aug 10 '15 at 12:46
  • 1
    I've just tried this on my 1.9 CE and although it works for products - the categories are not quite right. If I have a category called 'Test' which gives the url '.../test' and then I create another called 'Test' it should give the url '.../test-2' but instead gives just the number not the name: '.../-2' – odd_duck Aug 26 '16 at 15:41
10

After you applied the patch posted by Simon, you can use the following query to remove junk data:

DELETE FROM core_url_rewrite
WHERE is_system <> 1 AND id_path REGEXP "^[0-9]+_[0-9]+$" AND
      (request_path REGEXP ".*-[0-9]*\.html" 
          OR target_path = request_path);

In contrast to Ashish Hira's query, this only effects URLs which have an integer number as there last part - this was - in my case - the reason for the clutter.

It tries to not touch valid rewrites, which for example might have been created when updating an URL key.

6

I've implemented the accepted answer with success. On another Magento install I needed to preserve some custom rewrites so I deleted all entries that ended in a - and then a number up to 5 digits long with:

DELETE FROM `core_url_rewrite` WHERE `request_path` REGEXP '\\-[0-9]{1,5}$';

This mostly worked but I still get 2 more rows on each re-index. Not sure why. I thought I would share this experience.

  • 1
    You probably deleted URLs that are valid, yet end in a number. You'd find those with $collection = Mage::getModel('catalog/product')->getCollection()->addAttributeToFilter('url_key', array('regexp' => '[0-9]$')); – Melvyn Mar 28 '15 at 17:43
5

The core change that you mentioned only seems to be needed if you have products without url_keys, however Magento should always create url_keys for you. If you have some importer which is creating products without url_keys, then this problem will crop up for those products. Try running the following query to find such products:

SELECT cpe.entity_id, cpe.sku, cpev.value
FROM catalog_product_entity cpe
LEFT JOIN catalog_product_entity_varchar cpev
ON cpe.entity_id = cpev.entity_id AND cpev.attribute_id = (
    SELECT attribute_id
    FROM eav_attribute
    WHERE `entity_type_id` = 4
    AND `attribute_code` = 'url_key'
)
WHERE cpev.value IS NULL OR cpev.value = ''

If any products return from that query, they don't have a url_key and are going to be a problem.

  • 2
    Mind that the default entity_type_id for products is 4 and not 10. – Simon Jan 20 '16 at 15:07
3

I followed approved solution to prevent duplicate URL rewrites, then exported core_url_rewrite as CSV file. Was able to open this CSV and delete all but manually created URL rewrites.

Then I truncated core_url_rewrite table, and imported my saved CSV with manually created URL rewrites.

After all changes, went from 940K rows to 32K. Huge improvement.

3

Here is patch (local rewrite) for Magento Community for fix that https://github.com/biotech/Magento-URL-Rewrite In fact does the same as EE patch PATCH_SUPEE-389_EE_1.12.0.2_v2.sh - check every rewrite and avoid creation of duplicated records. Works well last 2 months on production CE 1.9 , 15k products , 4 stores , full re-index every night after bulk product import changes.

  • How thoroughly has this been tested? It looks like it was posted just an hour ago.... – SR_Magento Aug 11 '15 at 15:20
  • Has this been fixed in 1.9.2.x so we no longer need worry about table bloat? – Fiasco Labs Aug 11 '15 at 15:21
  • Single link answers are not the best answers even if they might solve the problem. Please explain a bit what your code does. – Marius Aug 11 '15 at 15:23
  • @FiascoLabs yes, works good on all CE 1.9.x – FireBear Aug 12 '15 at 16:11
  • 1
    @FiascoLabs: 1.9.2.x still has this "rewrite bloat" issue, and doesn't include this fix, however, as FireBear said, the EE patch will work with CE 1.9.2.x. (have not tried personally; only wanted to clarify that 1.9.2.2 definitely still has this issue) – Eric Seastrand Jan 5 '16 at 23:06
2

Since this is not yet mentioned in this thread, I wanted to share the cool news that this issue is fixed in Magento 1.9.3.9 and later. See the related release notes:

Magento no longer performs unnecessary write operations on the core_url_rewrite table.

So all fixes for this issue mentioned here are not necessary when using a version of Magento greater or equal than 1.9.3.9. I still suggest deleting the old values as described in Alex answer.

1

Run this query

DELETE FROM core_url_rewrite WHERE is_system <> 1 AND id_path REGEXP "^[0-9]+_[0-9]+$";

This will surely help you reduce the size of core_url_size table by deleting junk data.

  • Are you sure this is junk data? I think it deleted also rewrites which were created when changing an URL key! – Alex Mar 15 '16 at 10:10
  • Check the regex. this mean which don't have valid id – Asish Hira Mar 15 '16 at 10:13
  • But those IDs are also created when changing the URL key manually in the backend. See also my answer. – Alex Mar 15 '16 at 11:07
0

Get rid of .html

  1. Do not use suffix .html
  2. Set in .htaccess

    ## Redirect all htmls.
    RewriteRule (.+)\.html$ /$1 [L,R=301]
    
  3. Erase all .html redirects:

    DELETE FROM core_url_rewrite WHERE request_path LIKE '%.html'
    
0

The official answer should be to install SUPEE-389. Simple as that.

It perfectly works with Magento CE since they share the very same piece of code in this area.

You can find the patch file here, https://gist.github.com/piotrekkaminski/c348538ca91ba35773be#file-patch_supee-389_ee_1-12-0-2_v2-sh

We had this issue and it generated thousands of new rows after each Catalog URL re-index. Now the problem is gone... except for the fact that we have to clean the DB.

The script provided here seems like a good start but it is not a perfect solution,

php shell/rewrites_doctor.php --remove_rewrites 4

See https://www.atwix.com/magento/duplicated-product-url-keys-in-community-edition/

-2

There is also a dedicated module https://github.com/vladsmirnov/url-rewrites, so you don’t have to re-apply the patch after the each Magento update. Module contains of two parts: actual module, to prevent duplication from now on and the shell script to cleanup the existing database.

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.