Magento 2 index management

Magento 2 Index Management: The Top-notch Guide for Developers

by Lily Tran

Index management in Magento 2 plays a crucial role in ensuring your store operates efficiently. By regularly updating and optimizing the index, you can significantly improve site performance, reduce loading times, and enhance the customer experience. Understanding how Magento 2 handles indexing helps store owners keep their data accurate, ensure searchability, and streamline product updates.

This guide explores the importance of Magento 2 index management, how it works, and the best practices for maintaining an optimized eCommerce store for speed and efficiency.

What is Indexing in Magento 2?

The Concept of Magento 2 Indexing

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 Magento 2 storefront performance. When the data is modified, it needs to be re-updated or renewed.

Magento has a very complicated structure that stores a lot of data (including catalog, prices, users, stores, …) in various database tables, it stores data by using Magento 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.

These are 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 in Magento 2.

>>> Looking to create a database table in Magento 2 effortlessly? LEARN NOW: How to Create Database Table in Magento 2

Indexing Types 

  • Full reindex: rebuilds every data table of index in the system. Full index in Magento 2 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, if you have changed the price of a product,  you just need to reindex this product price. Reindexing all products or running Full reindex in Magento 2 is not necessary in this case.

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

how the partial reindex workflow

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.

>>> Curious about how to ensure your Magento 2 store runs smoothly after data changes? Check out this helpful guide on How to Reindex in Magento 2 to learn simple methods to keep your store performance optimized.

Each index in Magento 2 has its own status. Based on these statuses, Magento indexers can be managed 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 the Database in the indexer_state table.

SELECT * FROM indexer_state;

indexer_state table

Magento 2 Index Management: Adjust 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, Magento 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 Magento 2 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 Magento indexers
  • Click Actions and choose a suitable mode
  • Click Submit to complete the process

Configure index management in Magento 2

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 Magento indexer)
bin/magento indexer:set-mode realtime catalogrule_product (used for one or some specific indexers, each indexer is separated by a space)

Mechanism of Indexation by Mview

Mview stands for Materialized View – a database object containing 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 Magento 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

How to Create a Custom Indexer in Magento 2

What are 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.

4 Steps to Create Custom Indexer in Magento 2

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

Step 1: Declare Indexer Configuration File

Create a Magento 2 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 Magento indexer. You can call this ID if you want to check the 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 Configuration File

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

What are The Index Commands for Magento 2?

#1: View the list of indexers

To check the list of Magento 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 Magento indexer status, you run the command line:

bin/magento indexer:status [indexer]

The result is:

Magento indexer 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 Magento indexer parameter, it runs reindex as below:

reindex command

⇒ Command for each indexer:

command for each reindex

#4: Magento 2 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 Magento indexer mode,  you run the command line:

bin/magento indexer:show-mode

The result is:

show indexer mode

Moreover, you can use this command line to change the Magento 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 Magento indexers are displayed as default.

indexer command

Enhance Magento Index Management

Batch Indexing Method

In case you need to work with a large amount of data, reindexing becomes a big issue. However, Magento chooses the most downloaded Magento indexers and allows 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 Magento 2 indexers:

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

You may also adjust Magento 2 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!

Using BSS Reindex From Backend Extension

Surely you will know how to reindex data by read-on in this blog manually, but what about if you just want a quick way to reindex your data from Magento, the Magento 2 Reindex from backend extension is for you!

The FREE Magento extension allows the admin to reindex data in the backend at any time without the help of developers.magento 2 reindex from backend index management

In addition, this Magento 2 indexer can notify the details of the indexer and allow the admin to limit the ability to do action in reindexing data.

magento 2 reindex from backend notification

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

Problems with Magento 2 Index Management and Solutions

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

  • Get stuck during running reindex:

stuck during running reindex

  • Get locked:

locked during running index

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

  • Reset command:
bin/magento indexer:reset catalogrule_product

reset magento 2 indexer

  • Reindex command:
bin/magento indexer:reindex catalogrule_product

command for each reindex

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

After you install Cron for indexers, if Magento 2 indexers are not reindexed or just partially reindexed, the reason may stay at the higher version_id  of indexers in the 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.

>>> Dive into the Ultimate Magento 2 Tutorial for begginers that covers everything you need to know, from installation to advanced customizations. Start learning today!

Conclusion

In conclusion, Magento 2 index management is important knowledge we should keep in 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.

BSS Commerce is one of the leading Magento extension providers and web development services in the world. With experienced and certified Magento developers, we commit to bring high-quality products and services to optimize our business effectively.

CONTACT NOW to let us know your problems. We are willing to support you every time.

Next Reading Suggestions

© 2019 BSS Commerce owned by THANH CONG INTER ., JSC. All Rights Reserved.
Business registration certificate no. 0106064469 issued by Hanoi Department of Planning and Investment on 19 December 2019.
Legal Representative: Mr. Nguyen Quang Trung.