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 :
    www.my-site.com/product.html
    Magento also creates:
    www.my-site.com/category/product.html

  • 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.

 

Benchmark

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 :)

28 réflexions au sujet de « Magento Patch: How to optimize re-index processing time for Url Rewrite ? »

    • 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 1.4.1.1
      Kind regards

  1. Ping : B&W » Magento Patch: How to optimize re-index processing time for Url Rewrite ?

    • 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.

  2. Merci pour ce module ! pour le job de reindex_clearcache avec Magento Enterprise ver. 1.10.1.1, 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

  3. 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

  4. 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…

  5. 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?

  6. 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!

Laisser un commentaire

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

Vous pouvez utiliser ces balises et attributs HTML : <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>