Hello all.
Slow queries log containts next query:
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`);it goes on 75 seconds (850000 products). I have profiled this query. Mysql creates a temporary table and takes up most of the execution time. AFAIK, it's bug of mysql optimization. Because when i delete all joins after catalog_product_relation (only simple products in store, this table is empty), the query execution time becomes 300ms.
What can I do? Change saving of product?
