magento2: Price indexer slow for configurable BaseStockStatusSelectProcessor

Preconditions (*)

Magento 2.4.2
Catalog with lot of configurable products

Steps to reproduce (*)

Reindex catalog_product_price

Expected result (*)

Fast

Actual result (*)

Slow (in my case it never ends)

While monitoring the queries while reindexing, I notice these queries took long time:

SELECT le.entity_id, i.customer_group_id, i.website_id, MIN( final_price ), MAX( final_price ), MIN( tier_price ) FROM catalog_product_index_price_replica AS i INNER JOIN catalog_product_super_link AS l ON l.product_id = i.entity_id INNER JOIN catalog_product_entity AS le ON le.entity_id = l.parent_id INNER JOIN cataloginventory_stock_item AS si ON si.product_id = l.product_id INNER JOIN cataloginventory_stock_item AS si_parent ON si_parent.product_id = l.parent_id WHERE ( si.is_in_stock = 1 ) OR ( si_parent.is_in_stock = 0 ) AND ( le.entity_id IN ( 171648, 171655 )) GROUP BY le.entity_id, customer_group_id, website_id

Notice the where condition:

( si.is_in_stock = 1 ) OR ( si_parent.is_in_stock = 0 ) AND ( le.entity_id IN ( 171648, 171655 ))

This is very slow, it returns (in my case 70.000+ products) the same products over and over for all the configurable product that this query is executed for.

It should be:

(( si.is_in_stock = 1 ) OR ( si_parent.is_in_stock = 0 )) AND ( le.entity_id IN ( 171648, 171655 ))

With this solution the result are only about the mention configurable product in entity_id.

Looks like the issue is in BaseStockStatusSelectProcessor

We have 300.000+ products and around 30.000+ configurable products. With this fix we can reindex in 9 minutes, without it we never manage to reindex until success.

Please provide Severity assessment for the Issue as Reporter. This information will help during Confirmation and Issue triage processes.

Severity: S0 - Affects critical data or functionality and leaves users without workaround.
Severity: S1 - Affects critical data or functionality and forces users to employ a workaround.
Severity: S2 - Affects non-critical data or functionality and forces users to employ a workaround.
Severity: S3 - Affects non-critical data or functionality and does not force users to employ a workaround.
Severity: S4 - Affects aesthetics, professional look and feel, “quality” or “usability”.

About this issue

Original URL
State: closed
Created 3 years ago
Reactions: 1
Comments: 20 (9 by maintainers)

Most upvoted comments

Hi @engcom-Lima,

We see this issue on 2.4.3:

INSERT INTO `catalog_product_index_price_cfg_opt_temp`
SELECT `le`.`entity_id`, `i`.`customer_group_id`, `i`.`website_id`, MIN(final_price), MAX(final_price), MIN(tier_price)
FROM `catalog_product_index_price` AS `i`
INNER JOIN `catalog_product_super_link` AS `l` ON l.product_id = i.entity_id
INNER JOIN `catalog_product_entity` AS `le` ON le.row_id = l.parent_id AND (le.created_in <= '1637600400' AND le.updated_in > '1637600400')
INNER JOIN `cataloginventory_stock_item` AS `si` ON si.product_id = l.product_id
INNER JOIN `cataloginventory_stock_item` AS `si_parent` ON si_parent.product_id = l.parent_id
WHERE (si.is_in_stock = 1) OR (si_parent.is_in_stock = 0) AND (le.entity_id IN (502243, 251917, 251927, 691473, 691475, 691476, 691507, 691505))
GROUP BY `le`.`entity_id`,`customer_group_id`,`website_id` 
ON DUPLICATE KEY UPDATE `min_price` = VALUES(`min_price`), `max_price` = VALUES(`max_price`), `tier_price` = VALUES(`tier_price`)

You can see that the WHERE clause is WHERE X OR Y AND Z; as per mysql docs AND is higher precedence then OR. Thus the above WHERE clause WHERE X OR Y AND Z is really: WHERE X OR (Y AND Z) which is not correct.

You can see that \Magento\ConfigurableProduct\Model\ResourceModel\Product\Indexer\Price\Configurable::fillTemporaryOptionsTable adds a where clause on the le.entity_id.

Also, you can see that the $this->baseSelectProcessor->process($select) call is made before the where adds the le.entity_id filter. (Line 214 vs line 229). You should be able to ‘reproduce’ by echoing the select after line 230; though I am not 100% of the sequence needed to hit line 229. (Full reindex vs partial). (You definitely need a configurable with options)

Also, in general may I recomend that you use the dev:query-log:enable command to track all query made to the DB?

steven-hoffman-jomashop on Dec 6, 2021