How to add custom indexer for slow MySQL queries in Magento2

In this guide, we will learn how we can create custom indexers for slow MySQL queries.

In Magento 2, indexers play a crucial role in improving the performance of your e-commerce store. They are responsible for indexing and organizing data to enable faster search and retrieval of information. Magento transforms data such as products, categories, customer etc., to improve the performance of your storefront using indexers. Magento has a very sophisticated architecture that stores lots of merchant data in many database tables. To optimize store performance, Magento stores the data into special tables using indexers.

Step 1: Create a new module using the guide.

Step 2: Lets create a simple API for retrieving the result of product attributes, like name , quantity, price using MySQL queries.

<?xml version="1.0"?>

<routes xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Webapi:etc/webapi.xsd">
    
    <route url="/V1/test/api/me" method="GET">
        <service class="Webkul\Test\Api\TestApiManagementInterface" method="getApiData"/>
        <resources>
            <resource ref="anonymous"/>
        </resources>
    </route>
    
</routes>

<?php

namespace Webkul\Test\Model;

class TestApiManagement implements \Webkul\Test\Api\TestApiManagementInterface
{
    const SEVERE_ERROR = 0;
    const SUCCESS = 1;
    const LOCAL_ERROR = 2;

    protected $productCollectionFactory;
    protected $attributeFactory;
    protected $attributeCollectionFactory;

    public function __construct(
        \Magento\Eav\Model\ResourceModel\Entity\AttributeFactory $attributeFactory,
        \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory $productCollectionFactory,
        \Webkul\Test\Model\ResourceModel\Attribute\CollectionFactory $attributeCollectionFactory
    ) {
        $this->productCollectionFactory = $productCollectionFactory;
        $this->attributeFactory = $attributeFactory;
        $this->attributeCollectionFactory = $attributeCollectionFactory;
    }

    /**
     * get test Api data.
     *
     * @api
     *
     * @param int $id
     *
     */
    public function getApiData($id)
    {
        $model = $this->productCollectionFactory
            ->create();
        $eavAttribute = $this->attributeFactory->create();
        $productAttributeId = $eavAttribute->getIdByCode('catalog_product', 'name');
        $proPriceAttId = $eavAttribute->getIdByCode('catalog_product', 'price');
        $proWeightAttId = $eavAttribute->getIdByCode('catalog_product', 'weight');
        
        $catalogProductEntityVarchar = $model->getTable('catalog_product_entity_varchar');
        $catalogProductEntityDecimal = $model->getTable('catalog_product_entity_decimal');
        $cataloginventoryStockItem = $model->getTable('cataloginventory_stock_item');
        $sql = $catalogProductEntityVarchar.' as cpev';
        $cond = 'e.entity_id = cpev.entity_id';
        $fields = ['product_name' => 'value'];
        $model->getSelect()
            ->join($sql, $cond, $fields)
            ->where('cpev.store_id = 0 AND cpev.attribute_id = '.$productAttributeId);

        $sql = $catalogProductEntityDecimal.' as cped';
        $cond = 'e.entity_id = cped.entity_id';
        $fields = ['product_price' => 'value'];
        $model->getSelect()
            ->join($sql, $cond, $fields)
            ->where('cped.store_id = 0 AND (cped.attribute_id =
            '.$proPriceAttId.')');
        $model->getSelect()->join(
            $cataloginventoryStockItem.' as csi',
            'e.entity_id = csi.product_id',
            ["product_qty" => "qty"]
        )->where("csi.website_id = 0 OR csi.website_id = 1");
        return $model->getData();
    }

}

Now let’s learn how we can achieve the same result using custom indexer to fasten the process.
Step 3: In your module’s etc directory, create a new XML file named your_index_name.xml.This file will define the configuration for your custom indexer. Specify the name, ID, and other settings for your indexer, such as the data source and the schedule for updating the index.

<?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="product_attributes_indexer" class="Webkul\Test\Model\Indexer\ProductAttributesIndexer" view_id="product_attributes_indexer" >
        <title translate="true">Product Attributes Indexer</title>
        <description translate="true">Indexes product attributes like name, price, and quantity</description>
    </indexer>
</config>

A change log table is created according to the naming rule – INDEXER_TABLE_NAME + ‘_cl’, in case of product_attributes_indexer it will be product_attributes_indexer_cl. The table contains the version_id auto-increment column and entity_id column that contains identifiers of entities to be re-indexed.

Step 4: In your module’s etc directory, create mview.xml which is used to track database changes for a certain entity and running change handle (execute() method).

<?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="product_attributes_indexer" class="Webkul\Test\Model\Indexer\ProductAttributesIndexer" group="indexer">
        <subscriptions>
            <table name="catalog_product_entity" entity_column="entity_id" />
        </subscriptions>
    </view>
</config>

Step 5: Implement the Indexer Class Webkul\Test\Model\Indexer\ProductAttributesIndexer that extends the \Magento\Framework\Indexer\AbstractProcessor class.

Basically, Indexer should be able to perform with 3 types of action :

• Row index : For single entity. executeRow($id) method will be call in this indexer process.
• List index : For processing set of entity. executeList(array $ids) method will be call in this indexer process.
• Full index : For processing all entities from specific dictionary. executeFull() method will be call in this indexer process.

<?php
namespace Webkul\Test\Model\Indexer;

use Magento\Framework\Indexer\ActionInterface;
use Magento\Framework\Mview\ActionInterface as MviewActionInterface;
use Magento\Framework\Indexer\IndexerInterfaceFactory;

class ProductAttributesIndexer implements ActionInterface, MviewActionInterface
{
    private $indexerFactory;

    public function __construct(
        IndexerInterfaceFactory $indexerFactory,
        \Magento\Framework\App\ResourceConnection $resourceConnection,
        \Magento\Eav\Model\ResourceModel\Entity\AttributeFactory $attributeFactory,
        \Webkul\Test\Model\ResourceModel\Attribute $indexattribute
    ) {
        $this->indexerFactory = $indexerFactory;
        $this->resource = $resourceConnection;
        $this->connection = $resourceConnection->getConnection();
        $this->attributeFactory = $attributeFactory;
        $this->productAttributesData = $indexattribute;
    }

    public function execute($ids)
    {
        $indexer = $this->indexerFactory->create()->load('product_attributes_indexer');
        if ($indexer->isInvalid()) {
            return;
        }
        $connection = $this->connection;
        $customTable = $this->productAttributesData->getMainTable();
        $productTable = $connection->getTableName('catalog_product_entity');
        $eavAttribute = $this->attributeFactory->create();
        $productAttributeId = $eavAttribute->getIdByCode('catalog_product', 'name');
        $proPriceAttId = $eavAttribute->getIdByCode('catalog_product', 'price');
        $proWeightAttId = $eavAttribute->getIdByCode('catalog_product', 'weight');
        $catalogProductEntityVarchar = $connection->getTableName('catalog_product_entity_varchar');
        $catalogProductEntityDecimal = $connection->getTableName('catalog_product_entity_decimal');
        $cataloginventoryStockItem = $connection->getTableName('cataloginventory_stock_item');
        $select = $connection->select()->from(['e' => $productTable], ['entity_id']);
       if (!empty($ids)) {
            $select->where(
                "e.entity_id IN(?)",
                $ids
            );
        }
        $sql = $catalogProductEntityVarchar.' as cpev';
        $cond = 'e.entity_id = cpev.entity_id';
        $fields = ['product_name' => 'value'];
        $select
            ->join($sql, $cond, $fields)
            ->where('cpev.store_id = 0 AND cpev.attribute_id = '.$productAttributeId);

        $sql = $catalogProductEntityDecimal.' as cped';
        $cond = 'e.entity_id = cped.entity_id';
        $fields = ['product_price' => 'value'];
        $select
            ->join($sql, $cond, $fields)
            ->where('cped.store_id = 0 AND (cped.attribute_id =
            '.$proPriceAttId.')');
        $select->join(
            $cataloginventoryStockItem.' as csi',
            'e.entity_id = csi.product_id',
            ["product_qty" => "qty"]
        )->where("csi.website_id = 0 OR csi.website_id = 1");
        $data = $connection->fetchAll($select);
        foreach ($data as $item) {
            $entityId = $item['entity_id'];
            $name = $item['product_name'];
            $price = $item['product_price'];
            $quantity = $item['product_qty'];
            //save data in your custom indexer table
            $connection->insertOnDuplicate($customTable, [
                'product_id' => $entityId,
                'name' => $name,
                'price' => $price,
                'qty' => $quantity
            ]);
        }
       
    }

    public function executeFull()
    {
        $this->execute([]);
    }

    public function executeList(array $ids)
    {
        $this->execute($ids);
    }

    public function executeRow($id)
    {
        $this->execute([$id]);
    }
}

Step 6: Configure Dependency Injection To ensure that your custom indexer is properly instantiated and used within the Magento system, you need to configure dependency injection.

<type name="Magento\Framework\Indexer\ConfigInterface">
        <arguments>
            <argument name="indexers" xsi:type="array">
                <item name="product_attributes_indexer" xsi:type="string">Webkul\Test\Model\Indexer\ProductAttributesIndexer</item>
            </argument>
        </arguments>
  </type>

Step7: Test and Verify Once you have implemented your custom indexer, it’s important to thoroughly test and verify its functionality. Run the necessary commands to trigger indexing and ensure that your data is properly indexed and searchable.
php bin/magento indexer:reindex product_attributes_indexer

Now let’s create the function in api model to use the above indexer result:

<?xml version="1.0"?>

<routes xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Webapi:etc/webapi.xsd">
    
    <route url="/V1/test/api/index" method="GET">
        <service class="Webkul\Test\Api\TestApiManagementInterface" method="getIndexData"/>
        <resources>
            <resource ref="anonymous"/>
        </resources>
    </route>
</routes>

public function getIndexData()
    {
        /** \Webkul\Test\Model\ResourceModel\Attribute\CollectionFactory */
        $attributeCollection = $this->attributeCollectionFactory->create();
        return $attributeCollection->getData();
    }

As you can check the result of both the process, it took half the time in fetching the result using the custom indexers result.

We hope it will help you. Thank you!!

If any issue or doubt please feel free to mention in comment section.

We would be happy to help. Happy Coding!!