Adobe Commerce is the same broken as Magento 2
Adobe Commerce has the same issues as legacy, well known by performance and its bugs Magento 2 Enterprise Edition. Adobe just changed the name of the product without fixing the issues.
Let's check issues with Enterprise feature Product Target rules. An open-source version of this software doesn’t have this crappy extension.
What are Target rules?
Dynamic Rule-Based Product Relations
Dynamically target customers to present up-sells, cross-sells, and related products based on their specific product selections. Rules are easily administered through a condition-based tool, allowing marketers to manage specific product suggestions, shopping cart price rules easily, and banners to any customer segment to encourage additional purchases.
Related product rules give you the ability to target the selection of products that are presented to customers as related products, up-sells, and cross-sells. Each product rule can be associated with a customer segment to produce a dynamic display of targeted merchandising.
Sounds interesting; however, let's check implementation. One of the fortune 1 asked to check why Adobe Commerce is so slow and identify the issue. After hours of the Magento archeology, I have found where the issue was. And the issue was in the Target rule Adobes Enterprise spaghetti code.
so the target rule module has several extremely slow SQL query which kills the MySQL DB
The Query is:
SELECT `e`.`entity_id` FROM `catalog_product_entity` AS `e` INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = ? AND price_index.customer_group_id = ? INNER JOIN `catalog_category_product_index_store?` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=? AND cat_index.visibility IN(?, ?) AND cat_index.category_id=? INNER JOIN `cataloginventory_stock_item` AS `at_inventory_in_stock` ON (at_inventory_in_stock.`product_id`=e.entity_id) AND ((at_inventory_in_stock.use_config_manage_stock = ? AND at_inventory_in_stock.manage_stock=? AND at_inventory_in_stock.is_in_stock=?) OR (at_inventory_in_stock.use_config_manage_stock = ? AND at_inventory_in_stock.manage_stock=?) OR (at_inventory_in_stock.use_config_manage_stock = ? AND at_inventory_in_stock.is_in_stock=?)) WHERE ((( e.row_id IN (SELECT `t`.* FROM (SELECT IFNULL(relation.parent_id, table.row_id) FROM `catalog_product_entity_varchar` AS `table` INNER JOIN `catalog_product_relation` AS `relation` ON table.row_id=relation.child_id WHERE (table.attribute_id=?) AND (table.store_id=?) AND (`table`.`value`=:targetrule_bind_?) UNION SELECT `table`.`row_id` FROM `catalog_product_entity_varchar` AS `table` WHERE (table.attribute_id=?) AND (table.store_id=?) AND (`table`.`value`=:targetrule_bind_?)) AS `t`) AND (`e`.`attribute_set_id`=?) AND e.row_id IN (SELECT `t`.* FROM (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=?) AND (table.store_id=?) AND (`table`.`value`=:targetrule_bind_?) UNION SELECT `table`.`row_id` FROM `catalog_product_entity_int` AS `table` WHERE (table.attribute_id=?) AND (table.store_id=?) AND (`table`.`value`=:targetrule_bind_?)) AS `t`) AND (SELECT COUNT(*) FROM `catalog_category_product` WHERE (product_id=e.entity_id) AND (FIND_IN_SET(`category_id`, :targetrule_bind_?))) > ?)) AND (`e`.`entity_id` NOT IN(?))) AND (e.created_in <= ?) AND (e.updated_in > ?) LIMIT ?
The duration of the Query is 365 ms!
And Adobe has this query on every Product page!
How to fix this issue? It is simple don’t use it. If you are using any Adobe Enterprise Commerce features, simply remove them to have a faster website. The site will be the same slow, however faster than with Enterprise modules.
I have already written why this type of queries is slow and why Magento 2 is broken in this post with the fix:
Magento Has the same issues as an Adobe Flash
What will be the causes that ultimately brought down the Magento software platform that helped birth the eCommerce we know and sometimes love today?
