[MySQL] Оптимизатор... млин....
От: Ромашка Украина  
Дата: 11.10.19 08:11
Оценка:
Привет всем.

Есть такой запрос:
INSERT INTO `catalog_category_product_index_store1` (`category_id`, `product_id`, `position`, `is_parent`, `store_id`, `visibility`) 
SELECT `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, `ccp`.`position`, 1 AS `is_parent`, 1 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` 
FROM `catalog_category_entity` AS `cc`
  INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc.entity_id
  INNER JOIN `catalog_product_website` AS `cpw` ON cpw.product_id = ccp.product_id
  INNER JOIN `catalog_product_entity` AS `cpe` ON ccp.product_id = cpe.entity_id
  INNER JOIN `catalog_product_entity_int` AS `cpsd` ON cpsd.entity_id = cpe.entity_id AND cpsd.store_id = 0 AND cpsd.attribute_id = 97
  LEFT JOIN `catalog_product_entity_int` AS `cpss` ON cpss.entity_id = cpe.entity_id AND cpss.attribute_id = cpsd.attribute_id AND cpss.store_id = 1
  INNER JOIN `catalog_product_entity_int` AS `cpvd` ON cpvd.entity_id = cpe.entity_id AND cpvd.store_id = 0 AND cpvd.attribute_id = 99
  LEFT JOIN `catalog_product_entity_int` AS `cpvs` ON cpvs.entity_id = cpe.entity_id AND cpvs.attribute_id = cpvd.attribute_id AND cpvs.store_id = 1
  LEFT JOIN `catalog_product_relation` AS `relation` ON cpe.entity_id = relation.parent_id
  LEFT JOIN `catalog_product_entity` AS `relation_product_entity` ON relation.child_id = relation_product_entity.entity_id
  LEFT JOIN `catalog_product_entity_int` AS `child_cpsd` ON child_cpsd.entity_id = relation_product_entity.entity_id AND child_cpsd.store_id = 0 AND child_cpsd.attribute_id = 97
  LEFT JOIN `catalog_product_entity_int` AS `child_cpss` ON child_cpss.entity_id = relation_product_entity.entity_id AND child_cpss.attribute_id = child_cpsd.attribute_id AND child_cpss.store_id = 1 
WHERE (ccp.product_id IN ('1046571') OR relation.child_id IN ('1046571'))
         AND (cpw.website_id = '1') 
        AND (IFNULL(cpss.value, cpsd.value) = 1) 
        AND (IFNULL(cpvs.value, cpvd.value) IN (2, 3, 4)) 
        AND (relation.child_id IS NULL OR IFNULL(child_cpss.value, child_cpsd.value) = 1) 
        AND  (cc.path LIKE '1/2/%') 
GROUP BY `cc`.`entity_id`, `ccp`.`product_id`,
     `visibility` 
 ON DUPLICATE KEY UPDATE `category_id` = VALUES(`category_id`), `product_id` = VALUES(`product_id`), `position` = VALUES(`position`), `is_parent` = VALUES(`is_parent`), `store_id` = VALUES(`store_id`), `visibility` = VALUES(`visibility`);


Выцеплен из slow queries от Magento 2. Профайлер показывает, что мыскль за каким-то ухом создает temporary table, на что уходит 74 секунды из 75. А самое интересное, что в таблице catalog_product_relation нет ни одной записи. То есть, если убрать из запроса всё, что касается catalog_product_relation, типа так:

INSERT INTO `catalog_category_product_index_store1` (`category_id`, `product_id`, `position`, `is_parent`, `store_id`, `visibility`) 
SELECT `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, `ccp`.`position`, 1 AS `is_parent`, 1 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` 
FROM `catalog_category_entity` AS `cc`
  INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc.entity_id
  INNER JOIN `catalog_product_website` AS `cpw` ON cpw.product_id = ccp.product_id
  INNER JOIN `catalog_product_entity` AS `cpe` ON ccp.product_id = cpe.entity_id
  INNER JOIN `catalog_product_entity_int` AS `cpsd` ON cpsd.entity_id = cpe.entity_id AND cpsd.store_id = 0 AND cpsd.attribute_id = 97
  LEFT JOIN `catalog_product_entity_int` AS `cpss` ON cpss.entity_id = cpe.entity_id AND cpss.attribute_id = cpsd.attribute_id AND cpss.store_id = 1
  INNER JOIN `catalog_product_entity_int` AS `cpvd` ON cpvd.entity_id = cpe.entity_id AND cpvd.store_id = 0 AND cpvd.attribute_id = 99
  LEFT JOIN `catalog_product_entity_int` AS `cpvs` ON cpvs.entity_id = cpe.entity_id AND cpvs.attribute_id = cpvd.attribute_id AND cpvs.store_id = 1
  /*LEFT JOIN `catalog_product_relation` AS `relation` ON cpe.entity_id = relation.parent_id
  LEFT JOIN `catalog_product_entity` AS `relation_product_entity` ON relation.child_id = relation_product_entity.entity_id
  LEFT JOIN `catalog_product_entity_int` AS `child_cpsd` ON child_cpsd.entity_id = relation_product_entity.entity_id AND child_cpsd.store_id = 0 AND child_cpsd.attribute_id = 97
  LEFT JOIN `catalog_product_entity_int` AS `child_cpss` ON child_cpss.entity_id = relation_product_entity.entity_id AND child_cpss.attribute_id = child_cpsd.attribute_id AND child_cpss.store_id = 1 */
WHERE (ccp.product_id IN ('1046571')/* OR relation.child_id IN ('1046571')*/)
         AND (cpw.website_id = '1') 
        AND (IFNULL(cpss.value, cpsd.value) = 1) 
        AND (IFNULL(cpvs.value, cpvd.value) IN (2, 3, 4)) 
        -- AND (relation.child_id IS NULL OR IFNULL(child_cpss.value, child_cpsd.value) = 1) 
        AND  (cc.path LIKE '1/2/%') 
GROUP BY `cc`.`entity_id`, `ccp`.`product_id`,
     `visibility` 
ON DUPLICATE KEY UPDATE `category_id` = VALUES(`category_id`), `product_id` = VALUES(`product_id`), `position` = VALUES(`position`), `is_parent` = VALUES(`is_parent`), `store_id` = VALUES(`store_id`), `visibility` = VALUES(`visibility`)
    ;


то он, млин, начинает выполняться не 75 секунд, а 300 миллисекунд. С тем же результатом, который нужен.

Вопрос в чём... Можно ли как-то вправить мозг оптимизатору мыскля? Может только для этого конкретного случая? Или таки придется лезть в дебри (не знаю куда легче — в magento или в mysql)?


Всё, что нас не убивает, ещё горько об этом пожалеет.
 
Подождите ...
Wait...
Пока на собственное сообщение не было ответов, его можно удалить.