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 at 5:20
  • @RohanHapani No, it is mysql slow log.
    – Ibrahim S
    Jul 5 at 5:22
  • Add php code also from where you print sql log. Jul 5 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 at 4:08
  • @GohilRajesh That is irrelevant :)
    – Ibrahim S
    Jul 6 at 5:54

Your Answer

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

Browse other questions tagged or ask your own question.