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

Reindex from backend by BSSCommerce

Surely you will know how to manually reindex data by read-on in this blog, 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 extension allows admin to reindex data in the backend at any time without the help of developers.

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

EXPLORE Reindex from Magento 2 backend to quickly make setting changes.

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

I. 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, 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: 

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 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 Database in the indexer_state table. 

SELECT * FROM indexer_state;

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, 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

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

V. Indexers

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 in Magento 2

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

VII. Manage Magento 2 Indexers

1. View the list of indexers

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

The result is:

2. View indexer status

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

The result is:

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:

⇒ Command for each indexer:

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:

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.

VIII. Enhance Index in Magento 2

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

IX. Problems with Index in Magento 2 and Solutions

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

• Get stuck during running reindex:

• Get locked

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

• Command : bin/magento indexer:reset catalogrule_product

• Command : bin/magento indexer:reindex catalogrule_product

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 Magento 2 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 Magento 2 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.

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.

Tran Van Toan

+ posts

• How to Use Routing in Magento 2?
• Quick Overview Of Object Manager In Magento 2
• Frequently Detect Server Information for Magento Health Check
• [Solved] Magento Not Sending Emails: How To Fix?