{"id":382556,"date":"2023-05-19T10:06:04","date_gmt":"2023-05-19T10:06:04","guid":{"rendered":"https:\/\/webkul.com\/blog\/?p=382556"},"modified":"2023-05-19T10:06:11","modified_gmt":"2023-05-19T10:06:11","slug":"how-to-add-custom-indexer-for-slow-mysql-queries-in-magento2","status":"publish","type":"post","link":"https:\/\/webkul.com\/blog\/how-to-add-custom-indexer-for-slow-mysql-queries-in-magento2\/","title":{"rendered":"How to add custom indexer for slow MySQL queries in Magento2"},"content":{"rendered":"\n

In this guide, we will learn how we can create custom indexers for slow MySQL queries.<\/p>\n\n\n\n

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\u00a0optimize store performance, Magento stores the data into special tables using indexers.<\/p>\n\n\n\n

Step 1: Create a new module using the guide<\/a>.<\/strong><\/p>\n\n\n\n

Step 2: Lets create a simple API for retrieving the result of product attributes, like name , quantity, price using MySQL queries.<\/strong><\/p>\n\n\n\n

<?xml version="1.0"?>\n\n<routes xmlns:xsi="http:\/\/www.w3.org\/2001\/XMLSchema-instance"\n        xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Webapi:etc\/webapi.xsd">\n    \n    <route url="\/V1\/test\/api\/me" method="GET">\n        <service class="Webkul\\Test\\Api\\TestApiManagementInterface" method="getApiData"\/>\n        <resources>\n            <resource ref="anonymous"\/>\n        <\/resources>\n    <\/route>\n    \n<\/routes><\/pre>\n\n\n\n
<?php\n\nnamespace Webkul\\Test\\Model;\n\nclass TestApiManagement implements \\Webkul\\Test\\Api\\TestApiManagementInterface\n{\n    const SEVERE_ERROR = 0;\n    const SUCCESS = 1;\n    const LOCAL_ERROR = 2;\n\n    protected $productCollectionFactory;\n    protected $attributeFactory;\n    protected $attributeCollectionFactory;\n\n    public function __construct(\n        \\Magento\\Eav\\Model\\ResourceModel\\Entity\\AttributeFactory $attributeFactory,\n        \\Magento\\Catalog\\Model\\ResourceModel\\Product\\CollectionFactory $productCollectionFactory,\n        \\Webkul\\Test\\Model\\ResourceModel\\Attribute\\CollectionFactory $attributeCollectionFactory\n    ) {\n        $this->productCollectionFactory = $productCollectionFactory;\n        $this->attributeFactory = $attributeFactory;\n        $this->attributeCollectionFactory = $attributeCollectionFactory;\n    }\n\n    \/**\n     * get test Api data.\n     *\n     * @api\n     *\n     * @param int $id\n     *\n     *\/\n    public function getApiData($id)\n    {\n        $model = $this->productCollectionFactory\n            ->create();\n        $eavAttribute = $this->attributeFactory->create();\n        $productAttributeId = $eavAttribute->getIdByCode('catalog_product', 'name');\n        $proPriceAttId = $eavAttribute->getIdByCode('catalog_product', 'price');\n        $proWeightAttId = $eavAttribute->getIdByCode('catalog_product', 'weight');\n        \n        $catalogProductEntityVarchar = $model->getTable('catalog_product_entity_varchar');\n        $catalogProductEntityDecimal = $model->getTable('catalog_product_entity_decimal');\n        $cataloginventoryStockItem = $model->getTable('cataloginventory_stock_item');\n        $sql = $catalogProductEntityVarchar.' as cpev';\n        $cond = 'e.entity_id = cpev.entity_id';\n        $fields = ['product_name' => 'value'];\n        $model->getSelect()\n            ->join($sql, $cond, $fields)\n            ->where('cpev.store_id = 0 AND cpev.attribute_id = '.$productAttributeId);\n\n        $sql = $catalogProductEntityDecimal.' as cped';\n        $cond = 'e.entity_id = cped.entity_id';\n        $fields = ['product_price' => 'value'];\n        $model->getSelect()\n            ->join($sql, $cond, $fields)\n            ->where('cped.store_id = 0 AND (cped.attribute_id =\n            '.$proPriceAttId.')');\n        $model->getSelect()->join(\n            $cataloginventoryStockItem.' as csi',\n            'e.entity_id = csi.product_id',\n            ["product_qty" => "qty"]\n        )->where("csi.website_id = 0 OR csi.website_id = 1");\n        return $model->getData();\n    }\n\n}<\/pre>\n\n\n\n
\"apiwithsql\"<\/figure>\n\n\n\n
Now let’s learn how we can achieve the same result using custom indexer to fasten the process.
Step 3<\/strong>: In your module’s etc directory, create a new XML file named your_index_name.xml<\/strong>.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.<\/p>\n\n\n\n
<?xml version="1.0" ?>\n<config xmlns:xsi="http:\/\/www.w3.org\/2001\/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Indexer\/etc\/indexer.xsd">\n    <indexer id="product_attributes_indexer" class="Webkul\\Test\\Model\\Indexer\\ProductAttributesIndexer" view_id="product_attributes_indexer" >\n        <title translate="true">Product Attributes Indexer<\/title>\n        <description translate="true">Indexes product attributes like name, price, and quantity<\/description>\n    <\/indexer>\n<\/config><\/pre>\n\n\n\n
A change log table is created according to the naming rule – INDEXER_TABLE_NAME + ‘_cl’<\/strong>, in case of\u00a0product_attributes_indexer<\/code>\u00a0it will be\u00a0product_attributes_indexer_cl<\/code>. The table contains the\u00a0version_id<\/code>\u00a0auto-increment column and\u00a0entity_id<\/code>\u00a0column that contains identifiers of entities to be re-indexed.<\/p>\n\n\n\n
Step 4<\/strong>: In your module’s etc directory, create mview.xml <\/strong> which is used to track database changes for a certain entity and running change handle (execute() method).<\/p>\n\n\n\n
<?xml version="1.0" encoding="UTF-8"?>\n<config xmlns:xsi="http:\/\/www.w3.org\/2001\/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Mview\/etc\/mview.xsd">\n    <view id="product_attributes_indexer" class="Webkul\\Test\\Model\\Indexer\\ProductAttributesIndexer" group="indexer">\n        <subscriptions>\n            <table name="catalog_product_entity" entity_column="entity_id" \/>\n        <\/subscriptions>\n    <\/view>\n<\/config><\/pre>\n\n\n\n
Step 5<\/strong>: Implement the Indexer Class  Webkul\\Test\\Model\\Indexer\\ProductAttributesIndexer <\/strong> that extends the \\Magento\\Framework\\Indexer\\AbstractProcessor<\/code> class.<\/p>\n\n\n\n
Basically, Indexer should be able to perform with 3 types of action :<\/p>\n\n\n\n