Magento Index Management

Indexing is important for Magento 2 as for all systems that process large amounts of data in multiple tables. It facilitates platform’s performance. In this article, we are going to discuss the core aspects of Magento index management that you need to be aware of and we also demonstrate how you can create your own custom index.

What is indexing in Magento 2?
Default indexes in Magento 2
Terminology of indexation
Magento 2 index update
Magento index management via admin panel
Index commands for Magento 2
Index Status Tables in Database
Mechanism of indexation by schedule, Mview
Partial and complete indexation
Magento index management FAQs

What is indexing in Magento 2?

An index is a table of a certain format in a database. Magento index processing converts data to increase performance. It is necessary because the data which is required for an online store operation is stored in a complex structure of tables. For example, if you want to find out the current product price, it involves connecting and processing a lot of tables and their connections.

In their turn, indexes store partly processed and formatted data decreasing server load and increasing the store’s speed. However, indexing is required after any change of the data that this index is applied to.

Default indexes in Magento 2

There are several out of the box indexes in Magento 2 Open Source:

Name	ID	Class	Description
Catalog Product Rule	catalogrule_product	Magento\CatalogRule\Model\Indexer\Product\ProductRuleIndexer
Catalog Rule Product	catalogrule_rule	Magento\CatalogRule\Model\Indexer\Rule\RuleProductIndexer
Catalog Search	catalogseatch_fulltext	Magento\CatalogSearch\Model\Indexer\Fulltext
Category Products	catalog_category_product	Magento\Catalog\Model\Indexer\Category\Product	It creates the category – products connection
Customer Grid	customer_grid	Magento\Framework\Indexer\Action\Entity	It restructures Customer Grid index
Design Config Grid	design_config_grid	Magento\Theme\Model\Indexer\Design\Config
Product Categories	catalog_product_category	Magento\Catalog\Model\Indexer\Product\Category	It creates product – categories connection
Product EAV	catalog_product_attribute	Magento\Catalog\Model\Indexer\Product\Eav	It restructures the EAV structure into a flat structure
Product Price	catalog_product_price	Magento\Catalog\Model\Indexer\Product\Price	It pre-calculates product prices
Stock	cataloginventory_stock	Magento\CatalogInventory\Model\Indexer\Stock

Magento 2 Commerce Edition includes all the indexes from Open Source as well as some additional ones:

Name	ID	Class	Description
Inventory	inventory	Magento\InventoryIndexer\Indexer\InventoryIndexer	Multi-Source Inventory index
Product/Target Rule	targetrule_product_rule	Magento\TargetRule\Model\Indexer\TargetRule\Product\Rule	It creates product – rule connection
Target Rule/Product	targetrule_rule_product	Magento\TargetRule\Model\Indexer\TargetRule\Rule\Product	It creates rule – product connection
Sales Rule	salesrule_rule	Magento\AdvancedSalesRule\Model\Indexer\SalesRule	Rule indexing

Besides the indexes listed above, you can also create a custom one. We are going to discuss it in detail below.

Magento 2 Indexation Terminology

Dictionary is the original regular data entered into the system.
Index is the representation of the original data for optimized reading and searching. It should be updated when there are any changes in the dictionary.
Indexer is an object that creates and updates an index.

Magento 2 index update

In order to operate up-to-date data, indexes should be updated from time to time. There are three ways to do it:

Scheduled updates (launched by cron);
Magento index update on save (It is launched when the data has been edited and saved);
Manual update (via terminal).

By the type of processed data, Magento reindex can be launched as:

Full reindex processes all the indexed data again. It can always be launched manually via console.
Partial reindex processes again only the data that has been changed.

The logic of Magento indexer operation is described below:

Magento index management via admin panel

In the admin panel, you can control index modes and observe the current information about the indexes (System> Index Management).

In the Mode column, we can see how index is updated. In the Status column, we can see whether reindex is required (Reindex Required) or not required (Ready). In the Updated column, you can see the date of the latest update.

On this page, we can change the reindex mode. In order to do it, you should choose the necessary mode in a checkbox and press Submit.

The default opportunities for managing and monitoring index operations are quite limited but there are multiple free modules for managing and reorganizing indexes from the Magento 2 admin panel.

Indexer for Magento 2

Optimize the indexing process in your Magento 2 backend.

Free download

What are the index commands for Magento 2?

We cannot launch reindex from the admin panel. In order to do it, we need access to the server via terminal. If you put in php bin/magento indexer, you will see the command description for indexing:

indexer:info Shows allowed Indexers.
indexer:reindex Reindexes Data
indexer:reset Resets indexer status to invalid
indexer:set-mode Sets index mode type
indexer:show-mode Shows Index Mode
indexer:status Shows status of Indexer

Executing this command, we can see how to work with the whole group of indexes as well as with specific ones adding their names as an argument.

indexer:info

This command will show you the list of all indexes as a code-name used in the system. We can use index code as an argument for all the commands listed below.

Here is the execution example:

indexer:show-mode

This command will show you the indexer modes.

indexer:set-mode {realtime|schedule} [indexer]

This command can be used to change reindex mode. realtime sets Update on Save; schedule sets reindex mode to Update by Schedule. Using the indexer argument, you can change the mode of a specific index. Using no argument, the mode will be set for all indexes.

indexer:status

This command demonstrates the indexer status. It is one more way to define whether reindex is required or data is up-to-date and reindex is not required.

indexer:reindex

It is the most often used command that launches reindexing. If there are no arguments, all the indexes will be reindexed. If you use index code as an [index] argument, it will launch reindex only for this one. It is very convenient as it allows launching reindex only for the indexes that require it.

indexer:reset

Sometimes an error may occur during reindexing and the process cannot finish. For example, after executing reindex command, we come across this:

In order to fix it, we can pause the index with the reset command In this situation, you should execute the php bin/magento indexer:reset stock command. It will unblock the reindex process.

Index Status Tables in Database

The following tables in a database are used for index status storage:

indexer_state
mview_state

Mechanism of indexation by schedule, Mview

When there are any changes in the object of a database that the Magento 2 system has indexes for, a new trigger appears in the table with the _cl postfix if there is such a table. If there is no such table, a new one is generated. The mview.xml files are responsible for defining the data which changes will be tracked in the _cl (change log) table.

For example, Magento/Catalog/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="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>
…
</config>

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

…

</subscriptions>

</view>

…

</config>

From this extract, you can see that the catalog_category_product_cl will be created. It will contain the information about the changes in catalog_category_entity and catalog_category_entity_int tables.

All the change log tables are created by a certain INDEXER_TABLE_NAME + ‘_cl’ rule. The tables contain the version_id columns as autoincrement and entity_id that contains the object identifier that needs to be reindexed. For every table listed in the table nodes, three types of MYSQL AFTER triggers are created: INSERT, UPDATE, DELETE.

For example, for the catalog_category_entity table, the following triggers will be created:

CREATE DEFINER=`******`@`%` TRIGGER trg_catalog_category_entity_after_insert AFTER INSERT ON e FOR EACH ROW
BEGIN
INSERT IGNORE INTO `catalog_category_product_cl` (`entity_id`) VALUES (NEW.`entity_id`);
END   

CREATE DEFINER=`******`@`%` TRIGGER trg_catalog_category_entity_after_update AFTER UPDATE ON e FOR EACH ROW
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

CREATE DEFINER=`******`@`%` TRIGGER trg_catalog_category_entity_after_delete AFTER DELETE ON e FOR EACH ROW
BEGIN
    INSERT IGNORE INTO `catalog_category_product_cl` (`entity_id`) VALUES (OLD.`entity_id`);
END

CREATE DEFINER=`******`@`%` TRIGGER trg_catalog_category_entity_after_insert AFTER INSERT ON e FOR EACH ROW

BEGIN

INSERT IGNORE INTO `catalog_category_product_cl` (`entity_id`) VALUES (NEW.`entity_id`);

END

CREATE DEFINER=`******`@`%` TRIGGER trg_catalog_category_entity_after_update AFTER UPDATE ON e FOR EACH ROW

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

CREATE DEFINER=`******`@`%` TRIGGER trg_catalog_category_entity_after_delete AFTER DELETE ON e FOR EACH ROW

BEGIN

INSERT IGNORE INTO `catalog_category_product_cl` (`entity_id`) VALUES (OLD.`entity_id`);

END

The Mview component checks version_id in the *_cl tables if it is different from the one stored in the mview_state table. If the numbers are different, reindex launches.

Partial and complete indexation

Here we are going to discuss partial and complete indexation in Magento 2.

Partial indexation

Partial indexation updates indexes only in the objects that were modified. Partial indexation occurs both with Update on Save and Update by Schedule index modes. It is an indexation type where the change log tables are used that we described above.

Complete indexation

Complete indexation is required by some extensions in Magento 2 as some indexes may become invalid. Complete indexation launches by cron schedule or manually.

Magento index management FAQs

How create custom index

In order to do it, we will create a custom module. First of all, the registration.php file:

Partner With Us Let's discuss how to grow your business. Get a Free Quote.

Talk to Igor

<?php
\Magento\Framework\Component\ComponentRegistrar::register(
   \Magento\Framework\Component\ComponentRegistrar::MODULE,
   'Belvg_CronIndexer',
   __DIR__
);

<?php

\Magento\Framework\Component\ComponentRegistrar::register(

\Magento\Framework\Component\ComponentRegistrar::MODULE,

'Belvg_CronIndexer',

__DIR__

);

And the etc/module.xml file:

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:noNamespaceSchemaLocation="urn:magento:framework:Module/etc/module.xsd">
   <module name="Belvg_CronIndexer" setup_version="1.0.0">
   </module>
</config>

<?xml version="1.0"?>

<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:noNamespaceSchemaLocation="urn:magento:framework:Module/etc/module.xsd">

</module>

</config>

So, now we define our index. We need to create a etc/indexer.xml file for it:

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Indexer/etc/indexer.xsd">
   <indexer id="belvg_customcron_indexer
" view_id="belvg_customcron_indexer
" class="Belvg\CronIndexer\Model\Indexer\Example">
       <title translate="true">Belvg Indexer Example</title>
       <description translate="true">Belvg custom indexer</description>
   </indexer>
</config>

<?xml version="1.0"?>

<indexer id="belvg_customcron_indexer

" view_id="belvg_customcron_indexer

" class="Belvg\CronIndexer\Model\Indexer\Example">

<title translate="true">Belvg Indexer Example</title>

<description translate="true">Belvg custom indexer</description>

</indexer>

</config>

In this file, we define a new indexer with attributes:

id is a new unique index identifier. It is necessary particularly for launching reindex via terminal.
view_id is an identifier in the mview.xml file.
class possesses certain processing methods (executeFull, executeList, executeRow)

Simple indexer should contain the following elements:

title of an index that will be displayed in index grid
description of an index that will be displayed in index grid

Then, we create a etc/mview.xml file. It is necessary to detect changes in table objects and turn on indexers.

<?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="belvg_customcron_indexer" class="Belvg\CronIndexer\Model\Indexer\Example" group="indexer">
       <subscriptions>
           <table name="catalog_product_entity" entity_column="entity_id" />
       </subscriptions>
   </view>
</config>

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

</subscriptions>

</view>

</config>

In this file, we define the identifier of our mview and class where the execute() method will launch if there are any data changes. In the subscriptions node, we define the tables that contain the table name and column which value will be transferred to the execute() method if the value is changed. So when the value of entity_id will be transferred to the Belvg\CronIndexer\Model\Indexer\Example::execute() method if the values are changed in the catalog_product_entity table.

In the indexer.xml and mview.xml files, we have defined the Belvg\CronIndexer\Model\Indexer\Example class where the methods for changing data processing should be. Now, we need to create Model/Indexer/Example.php

<?php
namespace Belvg\CronIndexer\Model\Indexer;

class Example implements \Magento\Framework\Indexer\ActionInterface, \Magento\Framework\Mview\ActionInterface
{
   /*
    * Used by mview, allows process indexer in the "Update on schedule" mode
    */
   public function execute($ids){
       //Used by mview, allows you to process multiple placed orders in the "Update on schedule" mode
   }

   /*
    * Will take all of the data and reindex
    * Will run when reindex via command line
    */
   public function executeFull(){
       //Should take into account all placed orders in the system
   }

   /*
    * Works with a set of entity changed (may be massaction)
    */
   public function executeList(array $ids){
       //Works with a set of placed orders (mass actions and so on)
   }

   /*
    * Works in runtime for a single entity using plugins
    */
   public function executeRow($id){
       //Works in runtime for a single order using plugins
   }
}

<?php

namespace Belvg\CronIndexer\Model\Indexer;

class Example implements \Magento\Framework\Indexer\ActionInterface, \Magento\Framework\Mview\ActionInterface

{

* Used by mview, allows process indexer in the "Update on schedule" mode

public function execute($ids){

//Used by mview, allows you to process multiple placed orders in the "Update on schedule" mode

}

* Will take all of the data and reindex

* Will run when reindex via command line

public function executeFull(){

//Should take into account all placed orders in the system

}

* Works with a set of entity changed (may be massaction)

public function executeList(array $ids){

//Works with a set of placed orders (mass actions and so on)

}

* Works in runtime for a single entity using plugins

public function executeRow($id){

//Works in runtime for a single order using plugins

}

How to make Magento 2 index automatically at a set time?

So, we need to change the indexing frequency through cron. By default, indexation is performed every minute. You can see it for yourself by opening the file vendor / magento / module-indexer / etc / crontab.xml.

<?xml version="1.0"?>
<!--
/**
* Copyright © Magento, Inc. All rights reserved.
* See COPYING.txt for license details.
*/
-->
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Cron:etc/crontab.xsd">
   <group id="index">
       <job name="indexer_reindex_all_invalid" instance="Magento\Indexer\Cron\ReindexAllInvalid" method="execute">
           <schedule>* * * * *</schedule>
       </job>
       <job name="indexer_update_all_views" instance="Magento\Indexer\Cron\UpdateMview" method="execute">
           <schedule>* * * * *</schedule>
       </job>
       <job name="indexer_clean_all_changelogs" instance="Magento\Indexer\Cron\ClearChangelog" method="execute">
           <schedule>0 * * * *</schedule>
       </job>
   </group>
</config>

<?xml version="1.0"?>

<!--

/**

* See COPYING.txt for license details.

-->

</job>

</job>

</job>

</group>

</config>

We are primarily interested in the <job name = “indexer_reindex_all_invalid”> tag. Five asterisks indicate that all invalid indexes are updated every minute. Asterisks are given according to this table:

You can also set the numerical values, with the asterisk simply meaning “every minute (hour, day, etc.)”. Therefore, if you want to update crown indexes only once a day at a certain time, you can write, for example, this: 0 4 * * *. This entry will indicate: 0 minutes – 4 hours – every day (4 am every day).

But we cannot make changes to the files that the vendor folder contains. For this you will need to create your own module. We can use the module created before Belvg_CronIndexer.

And now in the etc folder we will create a crontab.xml file with the following contents:

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Cron:etc/crontab.xsd">
   <group id="index">
       <job name="indexer_reindex_all_invalid" instance="Magento\Indexer\Cron\ReindexAllInvalid" method="execute">
           <schedule>0 4 * * *</schedule>
       </job></group></config>

<?xml version="1.0"?>

</job></group></config>

So this is how we redefined the time; now the reindexing of invalid indexes will be performed not every minute, but once a day.

This is all to the topic of index management in Magento 2. We hope this article was helpful to you. If you have any comments or questions, please leave them down below.

Partner With Us Looking for a partner to grow your business? We are the right company to bring your webstore to success. Talk to Igor

Tags:

4 Comments

Jekb says:

June 17, 2021 at 09:11

Your guides are always good. Very nice.
And I see there is a new face to your company, that appears 100x times on every page :D
Vishal Verma says:

August 2, 2020 at 09:55

Really Helpful and very knowledgeable, Thanks. Please share your other blogs as well on M2.
Ide says:

April 8, 2020 at 15:14

Thanks for this guide, this should be a default option in Magento. To many actions will cause a full reindex during the day.
Dhaval solanki says:

August 27, 2019 at 13:53

Really nice solution about Run cron job at particular time.
I was looking for something like this how can we override core module crontab.xml file.
This worked
Thanks.

Magento Index Management

What is indexing in Magento 2?

Default indexes in Magento 2

Magento 2 Indexation Terminology

Magento 2 index update

Magento index management via admin panel

Indexer for Magento 2

What are the index commands for Magento 2?

indexer:info

indexer:show-mode

indexer:set-mode {realtime|schedule} [indexer]

indexer:status

indexer:reindex

indexer:reset

Index Status Tables in Database

Mechanism of indexation by schedule, Mview

Partial and complete indexation

Partial indexation

Complete indexation

Magento index management FAQs

How create custom index

How to make Magento 2 index automatically at a set time?

Table of contents:

4 Comments

Post a new comment Cancel

Magento Index Management

Get interesting stuff and updates to your email inbox

What is indexing in Magento 2?

Default indexes in Magento 2

Magento 2 Indexation Terminology

Magento 2 index update

Magento index management via admin panel

Indexer for Magento 2

What are the index commands for Magento 2?

indexer:info

indexer:show-mode

indexer:set-mode {realtime|schedule} [indexer]

indexer:status

indexer:reindex

indexer:reset

Index Status Tables in Database

Mechanism of indexation by schedule, Mview

Partial and complete indexation

Partial indexation

Complete indexation

Magento index management FAQs

How create custom index

How to make Magento 2 index automatically at a set time?

Table of contents:

4 Comments

Post a new comment Cancel