Magento index – The basics, benefits and tips to avoid errors

Magento has an indexing method that increases page speed of the user interface. The so called EAV (Entity Attribute Value) is data storage solution which is very flexible, but its data reading process is not very fast. Magento indexing is aimed to solve this performance problem. In this article we are going to detail its advantages and disadvantages. To avoid misunderstandings, it is important to note right at the beginning that is NOT about adding indexes to MySQL tables.



What does Magento indexing mean?

Definition 1:

 

Indexing is the process when Magento transforms product, category etc. data in order to achieve the fastest request speed.

 

Definition 2:

Data storage in MySQL in order to have the most appropriate indexes for SQL requests.

 

What would happen if there were not indexing in Magento?

All the following examples below show that Magento indexes have significant benefits in terms of ecommerce store performance. Even in the case of only one product it is worth running the index, though you should keep in mind that with one product, there is only a little increase in speed. So if you did not use Magento indexing, the you would face some serious drawbacks:

  1. Prices would be calculated in terms of download speed, on the basis of shopping cart price rules and customer groups.
  2. Calculation of inventory for configurable and bundle products would take place only after loading the product collection.
  3. „Layered navigation” would be generated in real time on the basis of hundreds of product attributes.
  4. The products within a category’s sub-categories, would be queried in a recursive way.

 

Jargon in Magento indexing

Before describing the process of performing Magento index, it is important to clarify a few basic terms.

  • Indexed data – The data set which defines the user interface display
  • Indexer – Procedure suitable for creating data arrays
  • Index event – The event or moment when the source data is modified
  • Index process – The time when the indexer is running
  • Main controller – It gives tasks to the Index process

 

Magento Index process

Below you can see the flow chart of the Magento Index process:

 

Magento index flowchart

 

Indexing can be started by way of a Magento event and manually as well. During the index process, in the user interface, the data are read from the EAV tables, thus reindexing is not recommended only in special, justified cases.

 

Describing Magento Indexing

Catalog and product indexing is switched off by default with both Magento CE and Magento EE. To switch it on go to System -> Configuration -> CATALOG -> Catalog -> Frontend -> Use Flat Catalog Category and Use Flat Catalog Category lists and select YES.

After saving, you’ll be notified in the Magento notification area that a reindex is necessary.

 

Magento index switch on

The Magento notification area can be found in the admin panel, right below the menu.

 

How to run Magento indexing?

 

1) Magento admin

Go to System -> Index Management. Click th Select All link, select Reindex Data at the Actions option, then click Submit.

 

Magento index run admin

 

2) Shell script

Start a terminal, then go to the Magento source directory and give the following command:

php shell/indexer.php –reindexall

 

Magento index run shell script

 

You also have the possibility of running only one index if you run the indexer.php with the –reindex <indexer> parameter. For example, if you want to reindex catalog URLs, then run the following command:

php shell/indexer.php — reindex catalog_url

 

Magento index run shell script 2

 

If you set the info parameter with the indexer, then you will get a list of all the indexes and the index codes.

 

Magento index run shell script

 

+1 method: from PHP code

Run the following code for the total reindex:

<?php
/* @var $indexCollection Mage_Index_Model_Resource_Process_Collection */
$indexCollection = Mage::getModel('index/process')->getCollection();
foreach ($indexCollection as $index) {
    /* @var $index Mage_Index_Model_Process */
    $index->reindexAll();
}

If you want to update only one index, then the following code  is enough:

<?php
$process = Mage::getModel('index/indexer')->getProcessByCode('catalog_product_price');
$process->reindexAll();
}
instead of catalog_product_price insert the index name that you'd like to update.

 

 

How to speed up reading from Magento index table

It is possible to add MySQL table indexes to index tables. One way to do it is the manual method, e.g. in phpMyAdmin, but after a reindex these indexes will disappear because following every reindex, Magento deletes and creates index tables. So we need a more advanced solution.

In the example below we add indexes to a product index.

Let’s create a module and “subscribe to” the catalog_product_flat_prepare_indexes event in its config.xml in the following way:

<catalog_product_flat_prepare_indexes>
    <observers>
        <aion_catalog_product_flat_prepare_indexes>
            <type>singleton</type>
            <class>aion_catalog/observer</class>
            <method>catalogProductFlatPrepareIndexes</method>
        </aion_catalog_product_flat_prepare_indexes>
    </observers>
</catalog_product_flat_prepare_indexes>

You can see in the xml section above that before the product reindex, the catalogProductFlatPrepareIndexes method of the Aion_Catalog_Model_Observer class will be called.

At this moment the table is created, we only need to add the new indexes to the table:

 

/**
 * Add indexes to product flat table
 *
 * @param Varien_Event_Observer $observer observer
 *
 * @return void
 */
public function catalogProductFlatPrepareIndexes(Varien_Event_Observer $observer)
{
    /** @var Varien_Object $indexesObject */
    $indexesObject = $observer->getIndexes();
    /** @var array $indexes */
    $indexes = $indexesObject->getIndexes();
 
    /**
     * We add indexes to these fields for faster request processes
     */
    $addFields = array(
        'upload_date', 'news_to_date', 'special_price', 'special_from_date', 'special_to_date'
    );
 
    foreach ($addFields as $field) {
        $indexes['IDX_'.strtoupper($field)] = array(
            'type' => Varien_Db_Adapter_Interface::INDEX_TYPE_INDEX,
            'fields' => array($field)
        );
    }
 
    $indexesObject->setIndexes($indexes);
}

 

We can add other attributes to the $addFields array. Please not that it is not all column types to which you can add indexes.

 

Errors that my occur during indexing

It can happen that you see some indexes with a processing status for a long time in the Index Management menu. This means that PHP could not close the index_process_*.lock files in the var/locks folder. The easiest way to solve this problem is to delete the lock files.

Another common error appears when the flat table gets damaged and this makes indexing fail. In such a case, the damaged flat table can be deleted without a problem because when reindexing, Magento checks if the table exists or not and if not, it creates it.

The columns in MySQL has a maximum number of 4096, but in reality it is much smaller. It also depends on how many characters each column can store. Thus it can happen that the table cannot be created. This is a rather hidden error because you can only see in the admin panel that the table cannot be reindexed, but you will have to search for a reason with server settings.

If you encounter such a problem, it’s worth taking a look at the attributes – whether all of them are needed in the flat tables or not. If you manage to reduce the number and/or size of the attributes (e.g. using varchar instead of text), this problem can be easily solved.

Unfortunately, the “trickiest” error is when the index process halts. This can happen when memory or running time limit is low on the web server. You can resolve this issue by increasing memory to be used by PHP and extend runtime.

Another solution is to run indexing with a command which I described at “2. shell script “ in the “How to run Magento indexing?” section.

 

Disadvantages of Magento index

I don’t want to confuse anyone. Magento index is a very useful feature of Magento. But at the same time you need to know about its disadvantages as well. Here are some:

  1. It duplicates data. Since data are written to the EAV data structure in admin, but on frontend the data are read from the index table. Therefore every product data is duplicated in the database. If you do not pay attention to reindex, it can happen that you see different things on the user interface than in the admin area.
  2. It needs abundant resources. Magento indexing uses a lot of server capacity because it reads data from a lot of locations.
  3. It is easy to reach the maximum limit of MySQL column numbers. I’ve mentioned this in the previous section.

 

Summary

I hope I could show properly the possibilities in Magento index and also provided help in solving some common problems that may arise. If you need any help, we’d be happy to assist you. Feel free to ask any questions in the comments field.

 

 


  • Dimple Patel

    Hello,

    I have upgraded my Magento version from 2.1.0 to 2.1.7
    Earlier the reindex time for Product Categories was 00:03:08 and now it has increased to 01:12:00
    Can anyone help me understand the reason behind this?

  • Ralp Mancawan

    hello, I traced the magento 1.9 CE tables, its because we need to duplicated the products, categories and sellers using from 1 store to another store (another domain), so my question is after we duplicate the products (product custom options, varchar, etc..) the problem is it will not display the products automatically in the front-end, what we do is re-index everytime we duplicate the products, is there a way to avoid re-indexing or did we missed a table(s) to duplicate? I need answers asap. Thank you.

  • Ralp Mancawan

    hello, I traced the magento 1.9 CE tables, its because we need to duplicated the products, categories and sellers using from 1 store to another store (another domain), so my question is after we duplicate the products (product custom options, varchar, etc..) the problem is it will not display the products automatically in the front-end, what we do is re-index everytime we duplicate the products, is there a way to avoid re-indexing or did we missed a table(s) to duplicate? I need answers asap. Thank you.

Do you need our support?
  • Magento Site Check
  • Magento Code Audit
  • Magento SEO Audit
  • Magento Project Rescue
Request help

NEED A RELIABLE, PROFESSIONAL MAGENTO DEVELOPMENT PARTNER?

Contact us if you have any question or requirement related to the preparation of a new or renewal of an existing online store.

Next