Notre blog

Magento Patch: How to optimize re-index processing time for Url Rewrite ?

Magento Patch: optimize re-index processing time for Url Rewrite

In the context of the daily technologic watch of our R&D departement whose purpose is to find new ways to optimize e-commerce websites, we are releasing an optimization extension for one of Magento’s most greedy index, the URL index !

But first of all, what’s an index ?

In order to reduce the time spent in collecting specific data (such as stocks, URL, prices…) in the database, Magento gathers them in « index » tables.

You can imagine this tables as baskets and the data in them as fruits. It’s much more faster to give users the fruits from baskets than making round trips to your fruit stall ! 😉

However, before being able to give efficiently this data, Magento need to fill up or update its indexes. This is called the re-index process.

These processes can take more or less time to execute themselves depending on the volume of data of your store and your server’s capacities.

Observation and problems

Some of our clients possess a great number of distinct URLs, so the reindex of «Catalog Url Rewrites» would take up to several hours to execute, whether it would be started manually in the BackEnd or by CRON.

For big catalogs, the following problems would appear:

  • Changes made in the BackEnd that would need a URL re-index could not be updated frequently because of the duration of the process ;
  • The process may sometimes not end properly ;
  • If the process doen’t end properly, some URL may be « badly » rewritten and put at a disavantage your SEO ;
  • Executed by CRON, it is sometimes compulsory to upgrade your RAM memory (PHP-CLI) ;
  • As long as the URL re-index isn’t completed, other re-index processes can’t start.


So our R&D team asked itself:

Why is Magento’s URL re-index taking so long ?

We then noticed that Magento didn’t use some of the store’s and products’ settings.

  • In fact, if you use short URLs for your products, Magento still creates both possibles URLs (long and short) in its index table.
  • Exemple :
    for :
    Magento also creates:

  • If your product is disabled, hence is not visible on your store, Magento stills indexes its URL.
  • If your product has several categories and sub-categories, there will be as many URLs as categories even if they aren’t used on your FrontEnd.
  • Same for products that are « not visible individually » which are generally used as associated products to a configurable product.
    Their URL are created and stored in the database.

    Solving and benefits:

    The index optimization extension we developed allows a better control over the URLs that need to be generated (or not) and therefore improve the duration of the process.

    Benefits for your store:

  • The URL re-index only takes a few minutes instead of several hours (in certain cases) ;
  • Being faster to execute, the process doesn’t end with an error or pending message anymore  ;
  • You don’t have 404 error anymore, no more impact on your SEO ;
  • Your server’s RAM memory consumption is less important than before during the process.



To give a taste of what you can expect, here are the first results obtained of differents types of catalogs and configurations.

Exemple :

With 9500 references (SKU), shared (or not) between 900 categories / Sub-categories and with CMS pages, the number of generated URLs in the «Core_URL_Rewrite» table is about 80 000.

The re-index (launched manually or by CRON) lasts about 3h30 (12 600 seconds).

After installing our patch, the re-index’s duration is 1 minute !

Other examples in the following chart :

Magento re-index processing time Benchmark


We can see that the re-index is clearly faster.

We also noticed a slight improvement on the Front’s display performances.

Installation & settings

Our extension «Patch_index_URL» is compatible with all versions of Magento CE &EE :


First make a backup of your database then install the extension on your developpement environment but not production !

Configuration for Magento DnD-Patch-url-index

  • In your BackEnd, go to : > System > Configuration > Developer
  • If you can’t see the extension, purge the Magento Cache and sessions, log out and log in to your Back End.
  • Select YES on the field «Enable Optimisation»/li>
  • If you don’t need to generate URLs for disabled products or not visible individually products, select YES for the 2 other fields.
  • Save
  • Go to the Index Management and re-index only your URLs.


Depending on the settings you chose, the re-index should be much quicker.

Any feedback and benchmarks is welcome 🙂

    1. Hi,
      you tell in your message it’s compatible with 1.4 CE, but on the magento connect it’s from 1.5. Could you please confirm it works for 1.4CE ? I’ve
      Kind regards

  1. Ping : B&W » Magento Patch: How to optimize re-index processing time for Url Rewrite ?
  2. Excellent, 110000 sku’s, indexing would take around 36 hours, I managed to do this in 90 minutes with the help of this patch. No issues I can see so far.

    1. This Patch is stable, no issues we can see so far.
      But you should test it before on Pre-production.
      Plesase, give us your feedback and your benchmark if you can.

  3. Before: >50.000 URLs in core_url_rewrite
    After: ~25.500 URLs
    Time: Less than a minute (~55s)

    Further reindexes require even less time (~45s).

  4. Merci pour ce module ! pour le job de reindex_clearcache avec Magento Enterprise ver., les résultats sont (avec 2542 produits configurables -dont 1584 actifs- et 12151 simples -dont 11661 actifs-) :
    SANS le module : ~25min
    AVEC le module : ~11min

  5. Hey the Extension is awesome and works greate…
    but i found a little bug. so when we have two categorys (or products) in the tree with the same url key, so magento results in a execption

    exception ‘PDOException’ with message ‘SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘0-qarya-assiyahia’ for key ‘UNQ_YA_CATALOG_PRODUCT_ENTITY_URL_KEY_STORE_ID_VALUE » in /development/lib/Zend/Db/Statement/Pdo.php:228

  6. It was a life saver! took around 4 minutes, reindexed all the rewrites, and solved all the problems. Now my url-s are really SEO friendly!

  7. Module fonctionnel mais pourrait facilement être amélioré. On charge tous les produits et on les zappent si ils correspondent pas à nos paramétrages (en stock, type,..) au lieu de ne remonter que les produits qui nous intéressent dès le début…

  8. We found this after searching for a solution for a reindex of 30k products that takes 4+ hours.
    Installed it but can’t see it in the admin of Magento.
    Using version 1.7, tryed clearing cach, login logout but there is no option to enable it.

    Any suggestions?

  9. We have been looking for this for a long time.
    Our site with 30k products was very slow when it came to indexing.
    It took us 5+ hours to get the index ready.

    Now with this plugin it only takes 5 minutes!
    This is a life safer!
    Thanks alot! Awesome extension!

  10. Hi, how to launch the indexer via Cron? What url do we need to call via cron?
    This is not clear in your instructions. Thanks for any help and of course making the module,


  11. I have a problem in magento CE when i launch the catalog_url reindexation :
    PHP Fatal error: Call to a member function beginTransaction() on a non-object in app/code/core/Mage/Catalog/Model/Indexer/Url.php on line 255

  12. I’m currently doing the pre-production tests but it looks pretty promising.

    Our particularity is that all our products have a stock qty of 1. This means that as soon as we sell them, it goes out of stock and is not available on the store anymore. However, URL rewrite remains in the database and the table itself is like gigantic, and URL Rewrite indexing takes hours, litteraly. We needed a way to reindex only the products that are in stock, which this pluggin does not allow yet (but switching the products out of stock to disabled made the trick).

    Here are the results so far on pre-prod (quad core *G RAM) and 18.669 products in database (12.998 of them being out of stock). I flushed the core_url_rewrite table before each benchmark:
    – URL rewrite with no Opt: 124 seconds, 111.207 rows in core_url_rewrite
    – URL rewrite Full Opt but products out of stock enabled: 45 seconds, 36.959 rows in core_url_rewrite
    – URL rewrite Full Opt and products out of stock disabled: 19 seconds, 11.295 rows in core_url_rewrite

    Next step is to get a dump of the production database and perform the same tests (we have currently more than 7M rows in core_url_rewrite table, 6M being associated with products beoing out of stock).

  13. Excelente parche!!! Lo mejor que he visto en magento para realizar esta tarea!!! Y además de todo ES GRATIS!! Son ustedes unos fenómenos! Muchas gracias!!!!!
    Probado en Magento 1.8.1

    Excellent patch! The best I’ve seen on magento to do this task! And besides all It’s FREE! You are a phenomena! Thank you very much!!
    Tested on Magento 1.8.1

  14. I run a french site, i got the following warning in configuration -> Developper:

    Le modèle de l’indexer devrait étendre Mage_Index_Model_Indexer_Abstract.
    (indexer tempalte should extend Mage_Index…)

    And i got a error page when i go to the re-indexing page.

    Thanks for help – Magento

  15. I have about 7000 skus 23 stores on but it still times out when I re-index, and then shows as processing for many hours, am I doing something wrong.

  16. Does this work with I’m trying it, but it doesn’t seem to have made a difference. I activated it in my admin (set everything to « Yes » like in the picture). Did I miss a step?

  17. Well nice extension. Works great.

    I have a question…..
    We have a shop with 6626 sku’s and about 50 categories
    The table core_url_rewrites is about 1.3GB large with ~2,588,888 records…..

    That is too much (I believe).

    How can we reduce this? An option is of cource to truncate the table, but then we got a lot of 404, seems not an option.

    Is it possible to reduce the table core_url_rewrite without having a lot of 404?

  18. Will this work if I use SSH to rewrite URLs?

    I have tried it on the demo install, it always times out (no data received) or stuck on processing. Magento version 1.8.1 CE

    7000 SKUs 22 stores 40 categories

  19. About 120.000 references, 80.000 of them for configurable products (not visible individually), 5 stores, 5000 customers. Before module more than 4 days to index and errors afther errors, with module about 6 hours… Thanks

  20. Ping : De beste en slechtste Magento extensies - Byte Blog

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *