>Top-notch Guide on Magento 2 Index Every Developer Must Read

Top-notch Guide on Magento 2 Index Every Developer Must Read

Index in Magento 2 is a data structure that helps to improve the performance of data searches and queries on a table. Index is how Magento updates data changes such as products and catalogs to optimize and enhance the performance of the storefront. When the data is modified, it needs to be re-updated or renewed. For Magento has a very complicated structure that stores a lot of data (including catalog, prices, users, stores, …) in various database tables, Magento stores data by using indexers to optimize the efficiency of the storefront. 

For example, if you desire to change a product price from 5$ to $6, Magento needs to reindex this price adjustment to make it appear on the frontend. 

In case there is no index at all, Magento has to calculate the prices of every product promptly, from shopping cart price rule, bundle pricing, discount, tier pricing, and so on. The loading of product prices takes a lot of time and may cause customers to leave their shopping carts. 

*Note: Index terms should be in your mind: 

  • Dictionary: includes all original data that is entered into the system. 
  • Index: represents the original data to optimize searching and reading. Indexed must be updated whenever the original data (dictionary) changes. 
  • Indexer: is the object creating an index. 

I. Indexing Types 

  • Full reindex: rebuilds every data table of index in the system. Full index often occurs when you update a significant change like adding a new store or creating a new customer group. You can run the Full index by using the command line: bin/magento indexer:reindex
  • Partial reindex: only reindex the modified data. For example, you have changed the price of a product,  you just need to reindex this product price. Reindexing all products or running Full reindex is not necessary in this case. 

Let’s take a look at the following diagram to know how the partial reindex works: 

partial index

II. Index Status 

Magento 2 Indexer status can be divided into three different types depending on whether an index data is up to date: 

  • Valid: Data is synchronized and no need to reindex.
  • Invalid: Data is modified and needs to be reindexed. 
  • Working: the reindex is in progress. 

Each index has its own status. Based on these statuses, Magento can manage indexers more easily. You can check out index status by using some of the following methods: 

  • Navigate to System →Tool → Index Management.
  • Run the command line: bin/magento indexer:status.
  • Check Database in the indexer_state table. 

SELECT * FROM indexer_state;

magento 2 indexer status

III. Indexing Modes

Magento 2 reindex is processed in 2 modes: 

  • Update on Save: Index is updated right after you save data. 
  • Update on Schedule: Index is updated by Cronjob. 

The question now is Which mode should be set up for your store? 

  • If you are running a small store, the “Update on Save” mode is good enough because data is continuously updated. 
  • If you are running a big store along with many administrators, indexers will be triggered continuously when the admins are working in the backend. Hence, the “Update on Save” mode will frequently perform with MySQL, leading to taking hours to complete the process. To avoid this issue, it would be better for you to set up the mode of indexers as “Update on Schedule.” Then you just run indexers in need via the command line or Cronjob.

⇒ Two methods to adjust the index modes: 

Method 1: In the store admin 

  • Login to the admin dashboard
  • Navigate to System → Tool → Management 
  • Select indexers 
  • Click Actions and choose a suitable mode 
  • Click Submit to complete the process

Magento 2 indexer

Method 2: Use command lines

Run the command line: bin/magento indexer:set-mode {realtime|schedule} [indexer]

For example: if  you want to change from the Update by Schedule” mode to the Update on Save” mode, you can use the following command line: 

bin/magento indexer:set-mode realtime (used for every indexers)
bin/magento indexer:set-mode realtime catalogrule_product (used for one or some specific indexers, each indexer is separated by a space)

IV. Mview 

Mview stands for Materialized View – a database object contains the result of a query.

In Magento, you can declare Mview by creating the mview.xml file that is used to track changes in the database of a specific entity. 

To help you get an easy understanding, we will analyze a declaration code snippet in the Magento/Catalog/etc/mview.xml, which tracks the relation between category and product: 

<view id="catalog_category_product" class="Magento\Catalog\Model\Indexer\Category\Product" group="indexer">
   <subscriptions>

       <table name="catalog_category_entity" entity_column="entity_id" />

       <table name="catalog_category_entity_int" entity_column="entity_id" />

   </subscriptions>

</view>

In which: 

  • view: specifies an indexer. 

+ Id: is the indexer table’s name. 

+ Class: is the indexer executor. 

+ Group: defines the indexer group. 

  • subscriptions: a list of tables to track changes.
  • table: specifies tables to track and observe changes.
  • name: the name of an observable table.
  • entity_column: the id column of entity to be reindexed.

Using the above declaration, whenever one or many categories are saved, updated or deleted in the catalog_category_entity, the “execute” method of the Magento\Catalog\Model\Indexer\Category\Product will be called with argument ids including ids of entities from the column defined under entity_column attribute. If the indexer mode is Update on Save”, this method is called immediately after execution. In case the indexer mode is “Update by Schedule,” MYSQL triggers help the mechanism to create a record in the changelog table. 

A changelog table is generated based on the naming rule  – INDEXER_TABLE_NAME + ‘_cl (in the above mentioned case, the name is catalog_category_product_cl). The table includes the version_id auto-increment column and the entity_id that contains identifiers entities to be reindexed. For each table node, MYSQL AFTER triggers are automatically created for each possible event (INSERT, UPDATE, DELETE) by the framework.  In the case of catalog_category_entity, let’s use the following code snippet to create triggers. 

  • Performing INSERT 
BEGIN
    INSERT IGNORE INTO `catalog_category_product_cl` (`entity_id`) VALUES (NEW.`entity_id`);
END
  • Performing UPDATE
BEGIN

    IF (NEW.`entity_id` <=> OLD.`entity_id`
        OR NEW.`attribute_set_id` <=> OLD.`attribute_set_id`
        OR NEW.`parent_id` <=> OLD.`parent_id`
        OR NEW.`created_at` <=> OLD.`created_at`
        OR NEW.`path` <=> OLD.`path`
        OR NEW.`position` <=> OLD.`position`
        OR NEW.`level` <=> OLD.`level`
        OR NEW.`children_count` <=> OLD.`children_count`)
            THEN INSERT IGNORE INTO `catalog_category_product_cl` (`entity_id`) VALUES (NEW.`entity_id`);
    END IF;

END
  • Performing DELETE
BEGIN
    INSERT IGNORE INTO `catalog_category_product_cl` (`entity_id`) VALUES (OLD.`entity_id`);
END

V. Magento 2 Indexers

Indexer name Indexer method name Indexer class Description
Design Config Grid design_config_grid Magento\Theme\Model\Indexer\Design\Config
Customer Grid customer_grid Magento\Framework\Indexer\Action\Entity No support when the mode is “Update by Schedule”
Category products catalog_category_product Magento\Catalog\Model\Indexer\Category\Product
Product categories catalog_product_category Magento\Catalog\Model\Indexer\Product\Category
Product price catalog_product_price Magento\Catalog\Model\Indexer\Product\Price
Product entity attribute value catalog_product_attribute Magento\Catalog\Model\Indexer\Product\Eav
Stock cataloginventory_stock Magento\CatalogInventory\Model\Indexer\Stock
Catalog rule product catalogrule_rule Magento\CatalogRule\Model\Indexer\Rule\RuleProductIndexer
Catalog product rule catalogrule_product Magento\CatalogRule\Model\Indexer\Product\ProductRuleIndexer
Catalog search catalogsearch_fulltext Magento\CatalogSearch\Model\Indexer\Fulltext

If you desire to customize an indexer, you can install a plugin or override to change the resolution in the methods of Indexer class

  • executeFull: the method is called when you run indexing by the command line. It will reindex adequately for all ids of entities.
  • executeRow: the method will index the individual row. It is often called when you save ids of separated entities such as catalog_product, salesrule_rule, and so on. If you set the indexer mode as “Update by Schedule“,  it will create a virtual table with the “_cl” suffix. This table contains two columns: version_id and entity_id.
  • executeList: the method will find out the differences between last version_id and current version_id. Between that, it will index for all entity_ids.
  • execute: The method is called based on stored data in the mview_state table. It will reindex entities included in the miew_state table. 

VI. Custom Index 

As you know, Index in Magento 2 aims at optimizing the data query process. Hence, custom index is quite necessary for modules with sophisticated functions which use or query data from various tables to calculate.   Let’s take a look at the following instruction to create a custom index: 

Step 1: Declare indexer 

Create an indexer configuration file: app/code/Bss/CustomIndex/etc/indexer.xml

<?xml version="1.0" encoding="UTF-8"?>

<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Indexer/etc/indexer.xsd">

   <indexer id="bss_custom_index" view_id="bss_custom_index" class="Bss\CustomIndex\Model\Indexer\CustomIndexer" shared_index="bss_custom_index">

       <title translate="true">Bss custom index</title>

       <description translate="true">Bss custom index</description>

       <dependencies>

           <indexer id="catalog_product_price"/>

       </dependencies>

   </indexer>

</config>
  • id: is used to identify the indexer. You can call this id if you want to check status, mode and reindex by the command line. 
  • view_id: is the id of the view attribute and is defined in the Mview file. 
  • class: is the name of the class used to resolve indexer.
  • shared_index: is used to combine indexers into groups. When you reindex an indexer in the group, other indexers are labeled as Valid. 
  • title: is the title of the indexer.
  • description: is the description of the indexer.
  • dependencies: mark dependence between indexers. If indexer A is dependent on indexer B, the index A is called and runs whenever you run reindex for the indexer B. For the above file, when you reindex catalog_product_price, the bss_custom_index is also reindexed simultaneously. 

Step 2. Declare Mview 

Create a Mview configuration file: app/code/Bss/CustomIndex/etc/mview.xml 

<?xml version="1.0" encoding="UTF-8"?>

<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Mview/etc/mview.xsd">

   <view id="bss_custom_index" class="Bss\CustomIndex\Model\Indexer\CustomIndexer" group="indexer">

       <subscriptions>

           <table name="catalog_product_entity" entity_column="entity_id"/>

       </subscriptions>

   </view>

</config>

Step 3. Declare the method of class

Create a file: app/code/Bss/CustomIndex/Model/Indexer/CustomIndexer.php

<?php
namespace Bss\CustomIndex\Model\Indexer; 
use Magento\Framework\Mview\ActionInterface as MviewActionInterface;
use Magento\Framework\Indexer\ActionInterface as IndexerActionInterface;
class CustomIndexer implements MviewActionInterface, IndexerActionInterface
{
   /**
    * Execute full indexation
    *
    * @return void
    */
   public function executeFull()
   {
       // TODO: Implement executeFull() method.
   }
   /**
    * Execute partial indexation by ID list
    *
    * @param int[] $ids
    *
    * @return void
    */
   public function executeList(array $ids)
   {
       // TODO: Implement executeList() method.
   }
   /**
    * Execute partial indexation by ID
    *
    * @param int $id
    *
    * @return void
    */
   public function executeRow($id)
   {
       // TODO: Implement executeRow() method.
   }
   /**
    * Execute materialization on ids entities
    *
    * @param int[] $ids
    *
    * @return void
    * @api
    */
   public function execute($ids)
   {
       // TODO: Implement execute() method.
   }
}

Step 4. Clear cache

You should clear cache after completing all of these mentioned 3 steps. Then you navigate to System → Tool → Index Management to check the new custom index in the grid. 

clear cache

VII. Manage Magento 2 Indexers

1. View the list of indexers

To check the list of indexers, run the command line: bin/magento indexer:info.

The result is:

design_config_grid Design Config Grid
customer_grid Customer Grid
catalog_category_product Category Products
catalog_product_category Product Categories
catalogrule_rule Catalog Rule Product
catalog_product_attribute Product EAV
inventory Inventory
catalogrule_product Catalog Product Rule
cataloginventory_stock Stock
catalog_product_price Product Price
catalogsearch_fulltext Catalog Search

2. View indexer status

To get the indexer status, you run the command line: bin/magento indexer:status [indexer]

The result is:

magento 2 index status

3. Reindex

To reindex in Magento 2, you run the command line: bin/magento indexer:reindex [indexer]

If the command does not contain the indexer parameter, it runs reindex as below:

magento 2 reindex

⇒ Command for each indexer:

magento 2 reindex command line

4. Indexer Mode

As you already know, there are 2 modes for Magento 2 indexers: Update on save and Update by schedule.

To know the current indexer modeyou run the command line: bin/magento indexer:show-mode

The result is:

index mode magento 2

Moreover, you can use this command line to change the indexer mode: bin/magento indexer:set-mode {realtime|schedule} [indexer]

In which: 

  • Real time: selected indexers – index is updated immediately after data is changed (Update on save). 
  • Schedule: selected indexers – index is updated according to the cron job schedule (Update by schedule). 
  • Indexer: list of indexers (indexers are separated by a space). If you leave it blank, all indexers are displayed as default.

magento 2 indexer

VIII. Magento 2 Index Enhancement

In case you need to work with a large amount of data, reindexing becomes a big issue. However, Magento chooses the most downloaded indexers and allow batch indexing, which also helps us customize the data amount run each time. By using this method, users can adjust the amount of data based on the data type and size in the database. 

To manage this setting, you need to edit the batchRowsCount parameter in the di.xml file of the corresponding module. This feature is supported by the following indexers: 

  • Category Product Index (Catalog Module)
  •  Price Index (Catalog Module)
  • EAV Index (Catalog Module)
  •  Stock Index (CatalogInventory Module)

You may also adjust indexer efficiency by editing the index batching size variables. This controls how many entities are executed by indexers at one moment. Even in some cases, indexing time is reduced considerably. 

For example, a business has: 

  • 10 websites
  • 10 store groups
  • 20 store views
  • 300 tier prices
  • About 40,000 products (of which 254 are configurable)

Therefore, decreasing the batchRowsCount for catalog_product_price indexer from 5000 to 1000 (adjust batchRowsCount in vendor/magento/ module-catalog/etc/di.xml ) reduces the execution time from 4 hours to less than 2 hours. See more here!

IX. Problems with Magento 2 Index and Solutions

You may face two issues when working with Magento 2 Reindex: 

  • Get stuck during running reindex:

indexer gets stuck

  • Get locked

indexers get locked

Both of these cases are the catalogrule_product indexer gets stuck and locked, hence, you need to reset indexer and reindex by the following commands: 

  • Command : bin/magento indexer:reset catalogrule_product

magento 2 reset indexer

  • Command : bin/magento indexer:reindex catalogrule_product

magento 2 reindex

If prices, stock, or other attributes of products are not displayed exactly on the frontend, or products are not included in the category, or products are not displayed in the search results, you have to check whether reindex statuses are Valid or not. In case they are Invalid, you should reindex those indexers. If the statuses are Working, indexers may get stuck and you need to follow the above instruction to overcome this problem. 

After you install Cron for indexers, if indexers are not reindexed or just partially reindexed, the reason may stay at the higher version_id  of indexers in Mview_state table than that in changelog table (Mview section). Hence, the solution is to change the version_id of indexers in Mview_state to 0.

In conclusion, Index in Magento 2 is important knowledge we should be kept on mind when learning about Magento. Hence, it would be better for you to understand and apply it to your development or administration work.

If you have any questions or suggestions for the article, please leave us a comment to discuss together.

< Previous Post
Next Post >