Adobe Magento Commerce Indexer MVIEW Architecture

11 min readJul 12, 2021

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.

For example, if you change the price of an item from $4.99 to $3.99. Magento must reindex the price change to display it on your storefront.

Without indexing, Magento would have to calculate the price of every product on the fly, taking into account shopping cart price rules, bundle pricing, discounts, tier pricing, etc. Loading the price for a product would take a long time, possibly resulting in cart abandonment.

Magento Indexing Pathology

DictionaryOriginal data entered to the system. Dictionaries are organized in the normal form to facilitate maintenance. IndexRepresentation of the original data for optimized reading and searching. Indexes can contain results of aggregations and various calculations. Index data can be always re-created from a dictionary using a certain algorithm.IndexerObject that creates an index.

Magento uses MVIEW crapy architecture to reindex data asynchronously

Mview

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 changelog by last applied version_id and calls the execute method for each particular indexer with IDs as an argument.

MAgento triggers performance issue

MySQL stored procedures, functions and triggers, are tempting constructs for application developers. However, as I discovered, there can be an impact on database performance when using MySQL stored routines. Not being entirely sure of what I was seeing during a customer visit, I set out to create some simple tests to measure the impact of triggers on database performance. The outcome might surprise you.

Why stored routines are not optimal performance-wise

Recently, I worked with a customer to profile the performance of triggers and stored routines. Triggers can significantly slow down the response time of a query.

Triggers add overhead, as every kind of extra work. If you want performance, do the least possible amount of work using the fastest hardware (yes, I know it’s obvious).

Let's check what happened if you are upgrading a product with 10 attributes.

mysql> SHOW TRIGGERS LIKE "%catalog_product_ent%"\G

To insert 1 product and 10 attributes Magento requires at least 11 inserts. however, no with the pathological indexer functionality by adding 1 product MySQL requires 11 SQL — inserts for data values and also insert values into trigger tables. So what we will have

catalog_product_entity table requires insert into :

catalogsearch_fulltext_cl
catalogrule_product_cl
catalog_product_price_cl

+3 inserts

catalog_product_entity_* requires inserts into :catalogsearch_fulltext_clcatalog_product_attribute_clcatalogrule_product_clcatalog_product_price_cl

so for 10 attributes we will require 10 * 4 = 40 insert.

In total to insert one product you requires 11 + 3 +40 = 54 SQL inserts. And it is just for a real use case. in reality, you will have hundreds of attributes and to insert or upgrade a single product MySQL will execute 500+ SQL queries.

It is the worst architectural decision I have ever seen…

Magento 2 is just crapy software that big companies create for merchants and they are full of it.

What is the fix? Magento-less solution when you can remove all that Magento crap included all indexers not just a trigger and write the software and logic you need.

Magento presenting the indexer as something good. However, it is a workaround for bad architecture EAV decisions. And it is just a database pathology the Magento 2 PHP core code is more “beautiful”.

And issue even not in the trigger. The issue in the indexer itself. Each time you are changing something in Magento it riggers update all the indexers. Every minute Magento runs MVIEW indexers with super not optimized code and tt code kills the database and PHP server.

Let's check how many changes a real high-loaded Adobe Commerce with the dynamic product features has for 2 minutes.

All that in the backlog should be reindexed every minute…

So, for the high-loaded project with the dynamic product inventory into many orders per the second Magento is a huge overhead. Magento Adobe Commerce works ONL when pages are cached and served from the Varnish.

Below we can all triggers on Magento catalo_product_entit table.

*************************** 1. row ***************************
             Trigger: trg_catalog_product_entity_after_insert
               Event: INSERT
               Table: catalog_product_entity
           Statement: BEGIN
INSERT IGNORE INTO `catalogsearch_fulltext_cl` (`entity_id`) VALUES (NEW.`entity_id`);
INSERT IGNORE INTO `catalogrule_product_cl` (`entity_id`) VALUES (NEW.`entity_id`);
INSERT IGNORE INTO `catalog_product_price_cl` (`entity_id`) VALUES (NEW.`entity_id`);
END
              Timing: AFTER
             Created: 2021-07-06 08:47:45.53
            sql_mode: 
             Definer: @%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci*************************** 2. row ***************************
             Trigger: trg_catalog_product_entity_after_update
               Event: UPDATE
               Table: catalog_product_entity
           Statement: BEGIN
IF (NOT(NEW.`entity_id` <=> OLD.`entity_id`) OR NOT(NEW.`attribute_set_id` <=> OLD.`attribute_set_id`) OR NOT(NEW.`type_id` <=> OLD.`type_id`) OR NOT(NEW.`sku` <=> OLD.`sku`) OR NOT(NEW.`has_options` <=> OLD.`has_options`) OR NOT(NEW.`required_options` <=> OLD.`required_options`) OR NOT(NEW.`created_at` <=> OLD.`created_at`)) THEN INSERT IGNORE INTO `catalogsearch_fulltext_cl` (`entity_id`) VALUES (NEW.`entity_id`); END IF;
IF (NOT(NEW.`entity_id` <=> OLD.`entity_id`) OR NOT(NEW.`attribute_set_id` <=> OLD.`attribute_set_id`) OR NOT(NEW.`type_id` <=> OLD.`type_id`) OR NOT(NEW.`sku` <=> OLD.`sku`) OR NOT(NEW.`has_options` <=> OLD.`has_options`) OR NOT(NEW.`required_options` <=> OLD.`required_options`) OR NOT(NEW.`created_at` <=> OLD.`created_at`)) THEN INSERT IGNORE INTO `catalogrule_product_cl` (`entity_id`) VALUES (NEW.`entity_id`); END IF;
IF (NOT(NEW.`entity_id` <=> OLD.`entity_id`) OR NOT(NEW.`attribute_set_id` <=> OLD.`attribute_set_id`) OR NOT(NEW.`type_id` <=> OLD.`type_id`) OR NOT(NEW.`sku` <=> OLD.`sku`) OR NOT(NEW.`has_options` <=> OLD.`has_options`) OR NOT(NEW.`required_options` <=> OLD.`required_options`) OR NOT(NEW.`created_at` <=> OLD.`created_at`)) THEN INSERT IGNORE INTO `catalog_product_price_cl` (`entity_id`) VALUES (NEW.`entity_id`); END IF;
END
              Timing: AFTER
             Created: 2021-07-06 08:47:45.57
            sql_mode: 
             Definer: @%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 3. row ***************************
             Trigger: trg_catalog_product_entity_after_delete
               Event: DELETE
               Table: catalog_product_entity
           Statement: BEGIN
INSERT IGNORE INTO `catalogsearch_fulltext_cl` (`entity_id`) VALUES (OLD.`entity_id`);
INSERT IGNORE INTO `catalogrule_product_cl` (`entity_id`) VALUES (OLD.`entity_id`);
INSERT IGNORE INTO `catalog_product_price_cl` (`entity_id`) VALUES (OLD.`entity_id`);
END
              Timing: AFTER
             Created: 2021-07-06 08:47:45.61
            sql_mode: 
             Definer: @%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 4. row ***************************
             Trigger: trg_catalog_product_entity_datetime_after_insert
               Event: INSERT
               Table: catalog_product_entity_datetime
           Statement: BEGIN
INSERT IGNORE INTO `catalogsearch_fulltext_cl` (`entity_id`) VALUES (NEW.`entity_id`);
INSERT IGNORE INTO `catalogrule_product_cl` (`entity_id`) VALUES (NEW.`entity_id`);
INSERT IGNORE INTO `catalog_product_price_cl` (`entity_id`) VALUES (NEW.`entity_id`);
END
              Timing: AFTER
             Created: 2021-07-06 08:47:46.15
            sql_mode: 
             Definer: @%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 5. row ***************************
             Trigger: trg_catalog_product_entity_datetime_after_update
               Event: UPDATE
               Table: catalog_product_entity_datetime
           Statement: BEGIN
IF (NOT(NEW.`value_id` <=> OLD.`value_id`) OR NOT(NEW.`attribute_id` <=> OLD.`attribute_id`) OR NOT(NEW.`store_id` <=> OLD.`store_id`) OR NOT(NEW.`entity_id` <=> OLD.`entity_id`) OR NOT(NEW.`value` <=> OLD.`value`)) THEN INSERT IGNORE INTO `catalogsearch_fulltext_cl` (`entity_id`) VALUES (NEW.`entity_id`); END IF;
IF (NOT(NEW.`value_id` <=> OLD.`value_id`) OR NOT(NEW.`attribute_id` <=> OLD.`attribute_id`) OR NOT(NEW.`store_id` <=> OLD.`store_id`) OR NOT(NEW.`entity_id` <=> OLD.`entity_id`) OR NOT(NEW.`value` <=> OLD.`value`)) THEN INSERT IGNORE INTO `catalogrule_product_cl` (`entity_id`) VALUES (NEW.`entity_id`); END IF;
IF (NOT(NEW.`value_id` <=> OLD.`value_id`) OR NOT(NEW.`attribute_id` <=> OLD.`attribute_id`) OR NOT(NEW.`store_id` <=> OLD.`store_id`) OR NOT(NEW.`entity_id` <=> OLD.`entity_id`) OR NOT(NEW.`value` <=> OLD.`value`)) THEN INSERT IGNORE INTO `catalog_product_price_cl` (`entity_id`) VALUES (NEW.`entity_id`); END IF;
END
              Timing: AFTER
             Created: 2021-07-06 08:47:46.19
            sql_mode: 
             Definer: @%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 6. row ***************************
             Trigger: trg_catalog_product_entity_datetime_after_delete
               Event: DELETE
               Table: catalog_product_entity_datetime
           Statement: BEGIN
INSERT IGNORE INTO `catalogsearch_fulltext_cl` (`entity_id`) VALUES (OLD.`entity_id`);
INSERT IGNORE INTO `catalogrule_product_cl` (`entity_id`) VALUES (OLD.`entity_id`);
INSERT IGNORE INTO `catalog_product_price_cl` (`entity_id`) VALUES (OLD.`entity_id`);
END
              Timing: AFTER
             Created: 2021-07-06 08:47:46.22
            sql_mode: 
             Definer: @%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 7. row ***************************
             Trigger: trg_catalog_product_entity_decimal_after_insert
               Event: INSERT
               Table: catalog_product_entity_decimal
           Statement: BEGIN
INSERT IGNORE INTO `catalogsearch_fulltext_cl` (`entity_id`) VALUES (NEW.`entity_id`);
INSERT IGNORE INTO `catalog_product_attribute_cl` (`entity_id`) VALUES (NEW.`entity_id`);
INSERT IGNORE INTO `catalogrule_product_cl` (`entity_id`) VALUES (NEW.`entity_id`);
INSERT IGNORE INTO `catalog_product_price_cl` (`entity_id`) VALUES (NEW.`entity_id`);
END
              Timing: AFTER
             Created: 2021-07-06 08:47:45.77
            sql_mode: 
             Definer: @%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 8. row ***************************
             Trigger: trg_catalog_product_entity_decimal_after_update
               Event: UPDATE
               Table: catalog_product_entity_decimal
           Statement: BEGIN
IF (NOT(NEW.`value_id` <=> OLD.`value_id`) OR NOT(NEW.`attribute_id` <=> OLD.`attribute_id`) OR NOT(NEW.`store_id` <=> OLD.`store_id`) OR NOT(NEW.`entity_id` <=> OLD.`entity_id`) OR NOT(NEW.`value` <=> OLD.`value`)) THEN INSERT IGNORE INTO `catalogsearch_fulltext_cl` (`entity_id`) VALUES (NEW.`entity_id`); END IF;
IF (NOT(NEW.`value_id` <=> OLD.`value_id`) OR NOT(NEW.`attribute_id` <=> OLD.`attribute_id`) OR NOT(NEW.`store_id` <=> OLD.`store_id`) OR NOT(NEW.`entity_id` <=> OLD.`entity_id`) OR NOT(NEW.`value` <=> OLD.`value`)) THEN INSERT IGNORE INTO `catalog_product_attribute_cl` (`entity_id`) VALUES (NEW.`entity_id`); END IF;
IF (NOT(NEW.`value_id` <=> OLD.`value_id`) OR NOT(NEW.`attribute_id` <=> OLD.`attribute_id`) OR NOT(NEW.`store_id` <=> OLD.`store_id`) OR NOT(NEW.`entity_id` <=> OLD.`entity_id`) OR NOT(NEW.`value` <=> OLD.`value`)) THEN INSERT IGNORE INTO `catalogrule_product_cl` (`entity_id`) VALUES (NEW.`entity_id`); END IF;
IF (NOT(NEW.`value_id` <=> OLD.`value_id`) OR NOT(NEW.`attribute_id` <=> OLD.`attribute_id`) OR NOT(NEW.`store_id` <=> OLD.`store_id`) OR NOT(NEW.`entity_id` <=> OLD.`entity_id`) OR NOT(NEW.`value` <=> OLD.`value`)) THEN INSERT IGNORE INTO `catalog_product_price_cl` (`entity_id`) VALUES (NEW.`entity_id`); END IF;
END
              Timing: AFTER
             Created: 2021-07-06 08:47:45.82
            sql_mode: 
             Definer: @%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 9. row ***************************
             Trigger: trg_catalog_product_entity_decimal_after_delete
               Event: DELETE
               Table: catalog_product_entity_decimal
           Statement: BEGIN
INSERT IGNORE INTO `catalogsearch_fulltext_cl` (`entity_id`) VALUES (OLD.`entity_id`);
INSERT IGNORE INTO `catalog_product_attribute_cl` (`entity_id`) VALUES (OLD.`entity_id`);
INSERT IGNORE INTO `catalogrule_product_cl` (`entity_id`) VALUES (OLD.`entity_id`);
INSERT IGNORE INTO `catalog_product_price_cl` (`entity_id`) VALUES (OLD.`entity_id`);
END
              Timing: AFTER
             Created: 2021-07-06 08:47:45.85
            sql_mode: 
             Definer: @%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 10. row ***************************
             Trigger: trg_catalog_product_entity_int_after_insert
               Event: INSERT
               Table: catalog_product_entity_int
           Statement: BEGIN
INSERT IGNORE INTO `catalogsearch_fulltext_cl` (`entity_id`) VALUES (NEW.`entity_id`);
INSERT IGNORE INTO `catalog_product_category_cl` (`entity_id`) VALUES (NEW.`entity_id`);
INSERT IGNORE INTO `catalog_product_attribute_cl` (`entity_id`) VALUES (NEW.`entity_id`);
INSERT IGNORE INTO `catalogrule_product_cl` (`entity_id`) VALUES (NEW.`entity_id`);
INSERT IGNORE INTO `catalog_product_price_cl` (`entity_id`) VALUES (NEW.`entity_id`);
END
              Timing: AFTER
             Created: 2021-07-06 08:47:45.65
            sql_mode: 
             Definer: @%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 11. row ***************************
             Trigger: trg_catalog_product_entity_int_after_update
               Event: UPDATE
               Table: catalog_product_entity_int
           Statement: BEGIN
IF (NOT(NEW.`value_id` <=> OLD.`value_id`) OR NOT(NEW.`attribute_id` <=> OLD.`attribute_id`) OR NOT(NEW.`store_id` <=> OLD.`store_id`) OR NOT(NEW.`entity_id` <=> OLD.`entity_id`) OR NOT(NEW.`value` <=> OLD.`value`)) THEN INSERT IGNORE INTO `catalogsearch_fulltext_cl` (`entity_id`) VALUES (NEW.`entity_id`); END IF;
IF (NOT(NEW.`value_id` <=> OLD.`value_id`) OR NOT(NEW.`attribute_id` <=> OLD.`attribute_id`) OR NOT(NEW.`store_id` <=> OLD.`store_id`) OR NOT(NEW.`entity_id` <=> OLD.`entity_id`) OR NOT(NEW.`value` <=> OLD.`value`)) THEN INSERT IGNORE INTO `catalog_product_category_cl` (`entity_id`) VALUES (NEW.`entity_id`); END IF;
IF (NOT(NEW.`value_id` <=> OLD.`value_id`) OR NOT(NEW.`attribute_id` <=> OLD.`attribute_id`) OR NOT(NEW.`store_id` <=> OLD.`store_id`) OR NOT(NEW.`entity_id` <=> OLD.`entity_id`) OR NOT(NEW.`value` <=> OLD.`value`)) THEN INSERT IGNORE INTO `catalog_product_attribute_cl` (`entity_id`) VALUES (NEW.`entity_id`); END IF;
IF (NOT(NEW.`value_id` <=> OLD.`value_id`) OR NOT(NEW.`attribute_id` <=> OLD.`attribute_id`) OR NOT(NEW.`store_id` <=> OLD.`store_id`) OR NOT(NEW.`entity_id` <=> OLD.`entity_id`) OR NOT(NEW.`value` <=> OLD.`value`)) THEN INSERT IGNORE INTO `catalogrule_product_cl` (`entity_id`) VALUES (NEW.`entity_id`); END IF;
IF (NOT(NEW.`value_id` <=> OLD.`value_id`) OR NOT(NEW.`attribute_id` <=> OLD.`attribute_id`) OR NOT(NEW.`store_id` <=> OLD.`store_id`) OR NOT(NEW.`entity_id` <=> OLD.`entity_id`) OR NOT(NEW.`value` <=> OLD.`value`)) THEN INSERT IGNORE INTO `catalog_product_price_cl` (`entity_id`) VALUES (NEW.`entity_id`); END IF;
END
              Timing: AFTER
             Created: 2021-07-06 08:47:45.69
            sql_mode: 
             Definer: @%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 12. row ***************************
             Trigger: trg_catalog_product_entity_int_after_delete
               Event: DELETE
               Table: catalog_product_entity_int
           Statement: BEGIN
INSERT IGNORE INTO `catalogsearch_fulltext_cl` (`entity_id`) VALUES (OLD.`entity_id`);
INSERT IGNORE INTO `catalog_product_category_cl` (`entity_id`) VALUES (OLD.`entity_id`);
INSERT IGNORE INTO `catalog_product_attribute_cl` (`entity_id`) VALUES (OLD.`entity_id`);
INSERT IGNORE INTO `catalogrule_product_cl` (`entity_id`) VALUES (OLD.`entity_id`);
INSERT IGNORE INTO `catalog_product_price_cl` (`entity_id`) VALUES (OLD.`entity_id`);
END
              Timing: AFTER
             Created: 2021-07-06 08:47:45.73
            sql_mode: 
             Definer: @%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 13. row ***************************
             Trigger: trg_catalog_product_entity_text_after_insert
               Event: INSERT
               Table: catalog_product_entity_text
           Statement: BEGIN
INSERT IGNORE INTO `catalogsearch_fulltext_cl` (`entity_id`) VALUES (NEW.`entity_id`);
INSERT IGNORE INTO `catalogrule_product_cl` (`entity_id`) VALUES (NEW.`entity_id`);
END
              Timing: AFTER
             Created: 2021-07-06 08:47:45.90
            sql_mode: 
             Definer: @%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 14. row ***************************
             Trigger: trg_catalog_product_entity_text_after_update
               Event: UPDATE
               Table: catalog_product_entity_text
           Statement: BEGIN
IF (NOT(NEW.`value_id` <=> OLD.`value_id`) OR NOT(NEW.`attribute_id` <=> OLD.`attribute_id`) OR NOT(NEW.`store_id` <=> OLD.`store_id`) OR NOT(NEW.`entity_id` <=> OLD.`entity_id`) OR NOT(NEW.`value` <=> OLD.`value`)) THEN INSERT IGNORE INTO `catalogsearch_fulltext_cl` (`entity_id`) VALUES (NEW.`entity_id`); END IF;
IF (NOT(NEW.`value_id` <=> OLD.`value_id`) OR NOT(NEW.`attribute_id` <=> OLD.`attribute_id`) OR NOT(NEW.`store_id` <=> OLD.`store_id`) OR NOT(NEW.`entity_id` <=> OLD.`entity_id`) OR NOT(NEW.`value` <=> OLD.`value`)) THEN INSERT IGNORE INTO `catalogrule_product_cl` (`entity_id`) VALUES (NEW.`entity_id`); END IF;
END
              Timing: AFTER
             Created: 2021-07-06 08:47:45.93
            sql_mode: 
             Definer: @%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 15. row ***************************
             Trigger: trg_catalog_product_entity_text_after_delete
               Event: DELETE
               Table: catalog_product_entity_text
           Statement: BEGIN
INSERT IGNORE INTO `catalogsearch_fulltext_cl` (`entity_id`) VALUES (OLD.`entity_id`);
INSERT IGNORE INTO `catalogrule_product_cl` (`entity_id`) VALUES (OLD.`entity_id`);
END
              Timing: AFTER
             Created: 2021-07-06 08:47:45.96
            sql_mode: 
             Definer: @%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 16. row ***************************
             Trigger: trg_catalog_product_entity_tier_price_after_insert
               Event: INSERT
               Table: catalog_product_entity_tier_price
           Statement: BEGIN
INSERT IGNORE INTO `catalog_product_price_cl` (`entity_id`) VALUES (NEW.`entity_id`);
INSERT IGNORE INTO `catalogrule_product_cl` (`entity_id`) VALUES (NEW.`entity_id`);
END
              Timing: AFTER
             Created: 2021-07-06 08:47:44.04
            sql_mode: 
             Definer: @%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 17. row ***************************
             Trigger: trg_catalog_product_entity_tier_price_after_update
               Event: UPDATE
               Table: catalog_product_entity_tier_price
           Statement: BEGIN
IF (NOT(NEW.`value_id` <=> OLD.`value_id`) OR NOT(NEW.`entity_id` <=> OLD.`entity_id`) OR NOT(NEW.`all_groups` <=> OLD.`all_groups`) OR NOT(NEW.`customer_group_id` <=> OLD.`customer_group_id`) OR NOT(NEW.`qty` <=> OLD.`qty`) OR NOT(NEW.`value` <=> OLD.`value`) OR NOT(NEW.`website_id` <=> OLD.`website_id`) OR NOT(NEW.`percentage_value` <=> OLD.`percentage_value`)) THEN INSERT IGNORE INTO `catalog_product_price_cl` (`entity_id`) VALUES (NEW.`entity_id`); END IF;
IF (NOT(NEW.`value_id` <=> OLD.`value_id`) OR NOT(NEW.`entity_id` <=> OLD.`entity_id`) OR NOT(NEW.`all_groups` <=> OLD.`all_groups`) OR NOT(NEW.`customer_group_id` <=> OLD.`customer_group_id`) OR NOT(NEW.`qty` <=> OLD.`qty`) OR NOT(NEW.`value` <=> OLD.`value`) OR NOT(NEW.`website_id` <=> OLD.`website_id`) OR NOT(NEW.`percentage_value` <=> OLD.`percentage_value`)) THEN INSERT IGNORE INTO `catalogrule_product_cl` (`entity_id`) VALUES (NEW.`entity_id`); END IF;
END
              Timing: AFTER
             Created: 2021-07-06 08:47:44.08
            sql_mode: 
             Definer: @%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 18. row ***************************
             Trigger: trg_catalog_product_entity_tier_price_after_delete
               Event: DELETE
               Table: catalog_product_entity_tier_price
           Statement: BEGIN
INSERT IGNORE INTO `catalog_product_price_cl` (`entity_id`) VALUES (OLD.`entity_id`);
INSERT IGNORE INTO `catalogrule_product_cl` (`entity_id`) VALUES (OLD.`entity_id`);
END
              Timing: AFTER
             Created: 2021-07-06 08:47:44.11
            sql_mode: 
             Definer: @%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 19. row ***************************
             Trigger: trg_catalog_product_entity_varchar_after_insert
               Event: INSERT
               Table: catalog_product_entity_varchar
           Statement: BEGIN
INSERT IGNORE INTO `catalogsearch_fulltext_cl` (`entity_id`) VALUES (NEW.`entity_id`);
INSERT IGNORE INTO `catalog_product_attribute_cl` (`entity_id`) VALUES (NEW.`entity_id`);
INSERT IGNORE INTO `catalogrule_product_cl` (`entity_id`) VALUES (NEW.`entity_id`);
END
              Timing: AFTER
             Created: 2021-07-06 08:47:46.02
            sql_mode: 
             Definer: @%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 20. row ***************************
             Trigger: trg_catalog_product_entity_varchar_after_update
               Event: UPDATE
               Table: catalog_product_entity_varchar
           Statement: BEGIN
IF (NOT(NEW.`value_id` <=> OLD.`value_id`) OR NOT(NEW.`attribute_id` <=> OLD.`attribute_id`) OR NOT(NEW.`store_id` <=> OLD.`store_id`) OR NOT(NEW.`entity_id` <=> OLD.`entity_id`) OR NOT(NEW.`value` <=> OLD.`value`)) THEN INSERT IGNORE INTO `catalogsearch_fulltext_cl` (`entity_id`) VALUES (NEW.`entity_id`); END IF;
IF (NOT(NEW.`value_id` <=> OLD.`value_id`) OR NOT(NEW.`attribute_id` <=> OLD.`attribute_id`) OR NOT(NEW.`store_id` <=> OLD.`store_id`) OR NOT(NEW.`entity_id` <=> OLD.`entity_id`) OR NOT(NEW.`value` <=> OLD.`value`)) THEN INSERT IGNORE INTO `catalog_product_attribute_cl` (`entity_id`) VALUES (NEW.`entity_id`); END IF;
IF (NOT(NEW.`value_id` <=> OLD.`value_id`) OR NOT(NEW.`attribute_id` <=> OLD.`attribute_id`) OR NOT(NEW.`store_id` <=> OLD.`store_id`) OR NOT(NEW.`entity_id` <=> OLD.`entity_id`) OR NOT(NEW.`value` <=> OLD.`value`)) THEN INSERT IGNORE INTO `catalogrule_product_cl` (`entity_id`) VALUES (NEW.`entity_id`); END IF;
END
              Timing: AFTER
             Created: 2021-07-06 08:47:46.07
            sql_mode: 
             Definer: tilebarpro_prod_M2@%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 21. row ***************************
             Trigger: trg_catalog_product_entity_varchar_after_delete
               Event: DELETE
               Table: catalog_product_entity_varchar
           Statement: BEGIN
INSERT IGNORE INTO `catalogsearch_fulltext_cl` (`entity_id`) VALUES (OLD.`entity_id`);
INSERT IGNORE INTO `catalog_product_attribute_cl` (`entity_id`) VALUES (OLD.`entity_id`);
INSERT IGNORE INTO `catalogrule_product_cl` (`entity_id`) VALUES (OLD.`entity_id`);
END
              Timing: AFTER
             Created: 2021-07-06 08:47:46.10
            sql_mode: 
             Definer: tilebarpro_prod_M2@%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
21 rows in set (0.00 sec)

Adobe Magento Commerce Indexer MVIEW Architecture

Magento Indexing Pathology

Mview

MAgento triggers performance issue

Why stored routines are not optimal performance-wise

Written by Yegor Shytikov

Responses (1)