Create Indexer for Custom grid

[stanislav_ulozn]

Hi, I have a custom grid based on the sales_order_item table to which I joined additional columns. The additional columns make the loading of the grid very slow and it is getting slower with time so I decided to optimize this with a custom indexer.

How to go about creating an indexer for a table with additional join statements and then use that in my grid?

[shraddhash9dd4]

Hello,
Indexing is how Magento transforms data such as products and categories, to improve the performance of your storefront. As data changes, the transformed data must be updated or reindexed. Magento has a very sophisticated architecture that stores lots of merchant data (including catalog data, prices, users, and stores) in many database tables. To optimize storefront performance, Magento accumulates data into special tables using indexers.

The mview.xml file is used to track database changes for a certain entity.

For example part of Magento/Catalog/etc/mview.xml is tracking category to product relation described in the following record:

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

Explanation of nodes:

• The view node defines an indexer. The id attribute is a name of the indexer table, the class attribute is indexer executor, the group attribute defines the indexer group.
• The subscriptions node is a list of tables for tracking changes.
• The table node defines the certain table to observe and track changes. The attribute name is a name of an observable table, the attribute entity_column is an identifier column of entity to be re-indexed. So, in case of catalog_category_product, whenever one or more categories is saved, updated or deleted in catalog_category_entity the execute method of Magento\Catalog\Model\Indexer\Category\Product will be called with argument ids containing ids of entities from column defined under entity_column attribute. If indexer type is set to “Update on Save” the method is called right away after the operation. If it set to “Update by Schedule” the mechanism creates a record in the change log table using MYSQL triggers.

A change log table is created according to the naming rule - INDEXER_TABLE_NAME + ‘_cl’, in case of catalog_category_product it will be catalog_category_product_cl. The table contains the version_id auto-increment column and entity_id column that contains identifiers of entities to be re-indexed. For each table node the framework automatically creates MYSQL AFTER triggers for each possible event (INSERT, UPDATE, DELETE).

For the table catalog_category_entity triggers will be created with the following statements. INSERT operation:

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

UPDATE operation:

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

DELETE operation:

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

The method Magento\Framework\Mview\ViewInterface::update is responsible for handling records in the changelog. The method is being called by CRON and it defines IDs to be re-indexed from the change log by last applied version_id and calls the execute method for each particular indexer with IDs as an argument.

How to reindex

You can reindex by:

• Using a cron job, which is preferred because indexing runs every minute.
• Using the magento indexer:reindex [indexer] command, which reindexes selected indexers, or all indexers, one time only.
  To understand more follow below link:
  https://devdocs.magento.com/guides/v2.4/extension-dev-guide/indexing.html
  and if you want to add a custom indexer you can also do that .For custom indexer follow below link .
  https://devdocs.magento.com/guides/v2.4/extension-dev-guide/indexing-custom.html
  Happy Coding