Magento 2 EE - Target rule indexer took a long time to complete?

I have faced one issue in Magento 2.3.2 EE, that is whenever I added catalog related product rule in Admin -> Marketing -> Related Product Rule, that time I observed below indexer will take more time to complete, sometimes it crashed the Mysql server.


php bin/magento indexer:reindex targetrule_product_rule targetrule_rule_product

I have checked the running query using the SHOW FULL PROCESSLIST query and found the below query.

SELECT `e`.`entity_id` FROM `catalog_product_entity` AS `e` WHERE ((( e.row_id IN (SELECT IFNULL(relation.parent_id, table.row_id) FROM `catalog_product_entity_int` AS `table` INNER JOIN `catalog_product_relation` AS `relation` ON table.row_id=relation.child_id WHERE (table.attribute_id='1222') AND (table.store_id=0) AND (`table`.`value`='31') UNION SELECT `table`.`row_id` FROM `catalog_product_entity_int` AS `table` WHERE (table.attribute_id='1222') AND (table.store_id=0) AND (`table`.`value`='31')) AND  e.row_id IN (SELECT IFNULL(relation.parent_id, table.row_id) FROM `catalog_product_entity_int` AS `table` INNER JOIN `catalog_product_relation` AS `relation` ON table.row_id=relation.child_id WHERE (table.attribute_id='2105') AND (table.store_id=0) AND (`table`.`value`='4018') UNION SELECT `table`.`row_id` FROM `catalog_product_entity_int` AS `table` WHERE (table.attribute_id='2105') AND (table.store_id=0) AND (`table`.`value`='4018'))))) AND (e.created_in <= '123456') AND (e.updated_in > '123456')

When I run the above queries separately, it was loading very fast. It was kloading slowly when I run as a single query. Finally, I found the problem with IFNULL(...). The above query was generated from the below file.

vendor/magento/module-target-rule/Model/Rule/Condition/Product/Attributes/SqlBuilder.php

I have changed the return function as below to fix the query(took this patch from Magento 2.4.2 version). Please use a preference in di.xml to override the function.

   private function addGlobalAttributeConditions(
        $select,
        $condition,
        array &$bind
    ) {
    ...........................
    ...........................
    /** MySQL Subquery with IN statement performance optimizer */
    $selectWrapper = $this->indexResource->getConnection()->select()->from($resultSelect);
    return 'e.' . $linkField . ' IN (' . $selectWrapper . ')';
    }

After this fix, the indexer only took 7 seconds to complete.


I hope it helps. Thanks.

2 views0 comments

Recent Posts

See All