If you have been managing a Magento 1 store for any length of time, you may have encountered a silent performance killer: the core_url_rewrite table. It is not uncommon for a store with only 5,000 products to find this table ballooning to over 600,000 rows, consuming hundreds of megabytes of database space.
This bloat doesn't just take up disk space; it slows down every page load as Magento struggles to parse the massive index to find the correct URL. It can also lead to catastrophic performance during reindexing and significantly impact your SEO due to duplicate content issues. In this guide, we will explore why this happens and how to resolve it once and for all.
Understanding the core_url_rewrite Bloat
In a healthy Magento installation, the core_url_rewrite table should scale proportionally with your product and category count. However, due to a long-standing bug in the Magento 1 indexing logic, the system often generates duplicate URL keys for products every time a full reindex is triggered.
This usually happens because the indexer fails to recognize that a URL key already exists for a product. Instead of reusing the existing entry, it creates a new one, appending a suffix like -1, -2, or -3 to the URL. Over time, this creates a mountain of junk data that serves no purpose but to confuse search engines and slow down your database queries.
The SEO Impact of Duplicate Rewrites
While some developers suggest leaving the table alone to avoid breaking old URLs, an oversized core_url_rewrite table is actually an SEO liability. When thousands of duplicate product URLs are generated, search engine crawlers may find and index multiple versions of the same product page. This dilutes your link equity and can lead to "duplicate content" penalties, which lower your overall search rankings.
Solution 1: The Manual Code Fix (Override)
One of the most effective ways to stop the growth of this table is to modify how Magento handles product request paths. The issue often resides in the Mage_Catalog_Model_Url class. By default, the logic checks if a product has a URL key; if it thinks it doesn't (even if it does), it generates a new one.
To fix this, you should create a local rewrite of the following file:
app/code/core/Mage/Catalog/Model/Url.php.
Warning: Never modify core files directly. Always use a local module override to ensure your changes are upgrade-safe.
Locate the getProductRequestPath() method (around line 807) and find this block:
if ($product->getUrlKey() == '' && !empty($requestPath)
&& strpos($existingRequestPath, $requestPath) === 0
)
Change it to the following:
if (!empty($requestPath)
&& strpos($existingRequestPath, $requestPath) === 0
)
This change removes the empty check for the URL key, forcing Magento to be more conservative when deciding whether to generate a new path. This prevents the indexer from creating unnecessary increments for existing products.
Solution 2: Applying the SUPEE-389 Patch
For those running Enterprise Edition (EE) or older versions of Community Edition (CE), the official fix is the SUPEE-389 patch. This patch introduces more robust logic for generating unused paths and ensures that the indexer doesn't blindly increment URL suffixes.
The patch modifies getUnusedPathByUrlkey, implementing a regex-based check to see if a similar path already exists before creating a new one. Here is the core logic introduced by the patch:
// match request_url abcdef1234(-12)(.html) pattern
$match = array();
$regularExpression = '#(?P<prefix>(.*/)?' . preg_quote($urlKey) . ')(-(?P<increment>[0-9]+))?(?P<suffix>'
. preg_quote($suffix) . ')?$#i';
if (!preg_match($regularExpression, $requestPath, $match)) {
return $this->getUnusedPathByUrlkey($storeId, '-', $idPath, $urlKey);
}
$match['prefix'] = $match['prefix'] . '-';
$match['suffix'] = isset($match['suffix']) ? $match['suffix'] : '';
If you are on a version of Magento older than 1.9.3.9, applying this patch (or the logic within it) is highly recommended as it addresses the architectural flaw in the indexer.
Solution 3: Cleaning the Database
Once you have fixed the code to prevent future bloat, you must clean up the existing mess. There are two main ways to do this: the "Nuclear Option" and the "Surgical Cleanup."
The Nuclear Option: Truncate and Reindex
If you don't have many manual URL rewrites (custom redirects you created yourself), the fastest way is to truncate the table:
- Backup your database.
- Run the SQL command:
TRUNCATE TABLE core_url_rewrite;. - In the Magento Admin, go to System > Index Management and reindex Catalog URL Rewrites.
- Flush all caches.
The Surgical Cleanup: SQL Removal
If you need to preserve manual rewrites, you can use a targeted SQL query to remove only the system-generated junk. This query targets rows that are not marked as "system" and follow the common duplicate pattern:
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);
This query specifically removes entries that have been incremented with numbers (e.g., product-123.html) or entries where the target and request paths are identical, which are often redundant.
Best Practices for URL Management
To keep your core_url_rewrite table lean and healthy, follow these best practices:
- Maintain Unique URL Keys: Ensure all products have a unique
url_keyattribute. You can check for missing keys with this query:sql SELECT entity_id, sku FROM catalog_product_entity WHERE entity_id NOT IN (SELECT entity_id FROM catalog_product_entity_varchar WHERE attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'url_key')); - Upgrade to Magento 1.9.3.9+: If possible, upgrade to Magento 1.9.3.9 or later. Official release notes confirm that Magento no longer performs unnecessary write operations on this table in these versions.
- Monitor Table Size: Periodically check the row count of your table. If it exceeds 10x your product count, you likely have a logic issue that needs addressing.
Frequently Asked Questions
Will truncating the table hurt my SEO?
Truncating will remove all 301 redirects that Magento automatically created when you changed product names in the past. If you have changed many URLs recently, those old links will return 404 errors. However, for many stores, the performance gain and removal of duplicate content issues outweigh this risk. Always analyze your traffic before a full truncate.
Why does the table keep growing even after a truncate?
This usually happens because you have duplicate product names or categories. When Magento finds two products that would result in the same URL, it is forced to append a number to one of them. Ensure your product names or URL keys are unique to prevent this.
Does this issue affect Magento 2?
Magento 2 handles URL rewrites differently, using a more modular approach with the url_rewrite table. While it is less prone to the specific "infinite increment" bug of Magento 1, it can still grow large if you frequently change URL keys without cleaning up old entries.
Wrapping Up
An oversized core_url_rewrite table is more than just a storage issue; it is a bottleneck for your entire Magento store. By applying the code fixes found in SUPEE-389, ensuring your products have unique URL keys, and performing a one-time database cleanup, you can drastically improve your site's speed and SEO health.
Don't wait until your database reaches several gigabytes—check your table size today and take control of your Magento performance.
