June 25^th 2015

photo credit: triple-bacon-cheese.jpg via photopin (license)

How to solve the ever-growing core_url_rewrite table in Magento

Tags: bug, indexer, Magento

Got an ever-growing core_url_rewrite table in Magento? Don’t know what’s going on? I encountered this problem today: when I re-indexed my catalog URL’s, for some reason Magento added some extra rows. And it would keep on doing this each time I re-indexed it, when no product were even added, deleted or even modified. This was clearly a bug. But how to fix this?

The underlying cause

If you encounter this bug, chances are that you have one or more categories and/or products that share a similar product key. When this happens, Magentos’ indexer goes berserk and creates an ever-inflating table in your database, drastically decreasing performance on it’s way.

The fix

Lucky for you, you’re not the only one who encounters this strange behaviour. Also, a patch was released late 2013 that addresses this issue. If you don’t want to apply the patch and rather want to create your own module with a custom rewrite, simple rewrite the Mage_Catalog_Model_Url -model and rewrite the getUnusedPath() -method. After the following lines:

if ($rewrite->getIdPath() == $idPath) {
    $this->_rewrite = $rewrite;
    return $requestPath;
}

if ($rewrite->getIdPath() == $idPath) {

$this->_rewrite = $rewrite;

return $requestPath;

}

Add this:

// 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();
}

// 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();

}

And replace this:

$match[1] = $match[1] . '-';

1	$match[1] = $match[1] . '-';

With this:

$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

1 2	$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

Visitors give this article an average rating of 4.3 out of 5.

How would you rate this article?

★ ★ ★ ★ ★

3 thoughts on “How to solve the ever-growing core_url_rewrite table in Magento”

Not Marco says:

July 17, 2015 at 11:12

So looking forward to your next blog post!!

Reply
1. Giel Berkers says:
  
  July 17, 2015 at 11:55
  
  Thanks not buddy!
  
  Reply
  1. cappie2000 says:
    
    December 13, 2016 at 15:07
    
    Would you mind releasing this as a module?
    
    Reply