Recently we did a mass attribute update, we have 40000+ sku. Indexing worked fine. But slowly the website went down with 300% CPU utilisation. Upon seeing the reason of it, it came out that there two different kinds of queries that were triggered back to back. One of the query which started with 'Insert into 'search temp' was resolved using the solution given here https://github.com/magento/magento2/issues/15545 . However, there is one more kind of query which is still choking the system is this
| SELECT `e`.*, IF(at_url_key.value_id > 0, at_url_key.value, at_url_key_default.value) AS `url_key`, `stock_status_index`.`stock_status` AS `is_salable` FROM `catalog_product_entity` AS `e` INNER JOIN `catalog_product_entity_varchar` AS `at_url_key_default` ON (`at_url_key_default`.`entity_id` = `e`.`entity_id`) AND (`at_url_key_default`.`attribute_id` = '119') AND `at_url_key_default`.`store_id` = 0 LEFT JOIN `catalog_product_entity_varchar` AS `at_url_key` ON (`at_url_key`.`entity_id` = `e`.`entity_id`) AND (`at_url_key`.`attribute_id` = '119') AND (`at_url_key`.`store_id` = 2) INNER JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id AND stock_status_index.website_id = 0 AND stock_status_index.stock_id = 1 WHERE (IF(at_url_key.value_id > 0, at_url_key.value, at_url_key_default.value) = 'yellowwhite-18ktgold-ruby-casual1') AND (stock_status_index.stock_status = 1) |
I believe this is being triggered because of the change in attribute that was in layered navigation, but how do i come out of it. I have tried disabling almost all the possible extensions that deals with urlRewrite and LayeredNavigation but this query still keeps coming back. Please help me getting this fixed. Our website is non-functional because of this issue.
