0

I am facing a very bad query that is consuming the database reflecting on the CPU badly, I need some guidance in how I can address it:

SELECT
 `t_d`.`attribute_id`,
 `e`.`entity_id`,
 `t_d`.`value` AS `default_value`,
 `t_s`.`value` AS `store_value`,
 IF(t_s.value_id IS NULL,
 t_d.value,
 t_s.value) AS `value` 
 FROM
 `catalog_product_entity_varchar` AS `t_d` 
 INNER JOIN
 `catalog_product_entity` AS `e` 
 ON e.entity_id = t_d.entity_id 
 LEFT JOIN
 `catalog_product_entity_varchar` AS `t_s` 
 ON t_s.attribute_id = t_d.attribute_id 
 AND t_s.entity_id = t_d.entity_id 
 AND t_s.store_id = ? 
 WHERE
 (
 e.entity_id IN (?)
 ) 
 AND (
 t_d.attribute_id IN (?)
 ) 
 AND (
 t_d.store_id = IFNULL(t_s.store_id, ?)
 ) 
 UNION
 ALL SELECT
 `t_d`.`attribute_id`,
 `e`.`entity_id`,
 `t_d`.`value` AS `default_value`,
 `t_s`.`value` AS `store_value`,
 IF(t_s.value_id IS NULL,
 t_d.value,
 t_s.value) AS `value` 
 FROM
 `catalog_product_entity_int` AS `t_d` 
 INNER JOIN
 `catalog_product_entity` AS `e` 
 ON e.entity_id = t_d.entity_id 
 LEFT JOIN
 `catalog_product_entity_int` AS `t_s` 
 ON t_s.attribute_id = t_d.attribute_id 
 AND t_s.entity_id = t_d.entity_id 
 AND t_s.store_id = ? 
 WHERE
 (
 e.entity_id IN (?)
 ) 
 AND (
 t_d.attribute_id IN (?)
 ) 
 AND (
 t_d.store_id = IFNULL(t_s.store_id, ?)
 ) 
 UNION
 ALL SELECT
 `t_d`.`attribute_id`,
 `e`.`entity_id`,
 `t_d`.`value` AS `default_value`,
 `t_s`.`value` AS `store_value`,
 IF(t_s.value_id IS NULL,
 t_d.value,
 t_s.value) AS `value` 
 FROM
 `catalog_product_entity_decimal` AS `t_d` 
 INNER JOIN
 `catalog_product_entity` AS `e` 
 ON e.entity_id = t_d.entity_id 
 LEFT JOIN
 `catalog_product_entity_decimal` AS `t_s` 
 ON t_s.attribute_id = t_d.attribute_id 
 AND t_s.entity_id = t_d.entity_id 
 AND t_s.store_id = ? 
 WHERE
 (
 e.entity_id IN (?)
 ) 
 AND (
 t_d.attribute_id IN (?)
 ) 
 AND (
 t_d.store_id = IFNULL(t_s.store_id, ?)
 ) 
 UNION
 ALL SELECT
 `t_d`.`attribute_id`,
 `e`.`entity_id`,
 `t_d`.`value` AS `default_value`,
 `t_s`.`value` AS `store_value`,
 IF(t_s.value_id IS NULL,
 t_d.value,
 t_s.value) AS `value` 
 FROM
 `catalog_product_entity_text` AS `t_d` 
 INNER JOIN
 `catalog_product_entity` AS `e` 
 ON e.entity_id = t_d.entity_id 
 LEFT JOIN
 `catalog_product_entity_text` AS `t_s` 
 ON t_s.attribute_id = t_d.attribute_id 
 AND t_s.entity_id = t_d.entity_id 
 AND t_s.store_id = ? 
 WHERE
 (
 e.entity_id IN (?)
 ) 
 AND (
 t_d.attribute_id IN (?)
 ) 
 AND (
 t_d.store_id = IFNULL(t_s.store_id, ?)
 ) 
 UNION
 ALL SELECT
 `t_d`.`attribute_id`,
 `e`.`entity_id`,
 `t_d`.`value` AS `default_value`,
 `t_s`.`value` AS `store_value`,
 IF(t_s.value_id IS NULL,
 t_d.value,
 t_s.value) AS `value` 
 FROM
 `catalog_product_entity_datetime` AS `t_d` 
 INNER JOIN
 `catalog_product_entity` AS `e` 
 ON e.entity_id = t_d.entity_id 
 LEFT JOIN
 `catalog_product_entity_datetime` AS `t_s` 
 ON t_s.attribute_id = t_d.attribute_id 
 AND t_s.entity_id = t_d.entity_id 
 AND t_s.store_id = ? 
 WHERE
 (
 e.entity_id IN (?)
 ) 
 AND (
 t_d.attribute_id IN (?)
 ) 
 AND (
 t_d.store_id = IFNULL(t_s.store_id, ?)
 )
6
  • It's profiler report? Jul 5, 2021 at 5:20
  • @RohanHapani No, it is mysql slow log.
    – Ibrahim S
    Jul 5, 2021 at 5:22
  • Add php code also from where you print sql log. Jul 5, 2021 at 5:23
  • any custom indexing added in magento?? if yes then change it to update on save instead of update on scheduled Jul 6, 2021 at 4:08
  • @GohilRajesh That is irrelevant :)
    – Ibrahim S
    Jul 6, 2021 at 5:54

1 Answer 1

1

Please try run a ANALYZE TABLE on all you Magento2 tables

mysqlcheck -a -A

This will force the internal MySQL indexes and innodb stats to be rebuilt for you tables.

This seems to be introduced in a few Magento 2 upgrades which I have performed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.