Problematic queries after upgrading Magento to 1.13.1

Question

We upgraded our Enterprise to 1.13.1 few days ago, to take advantage of the new indexing system, but one type of query has made our operations very slow - indexing, category saving and product saving all trigger this type of query at some point:

INSERT IGNORE INTO `catalog_category_product_index_tmp` (`category_id`, `product_id`, `position`, `is_parent`, `store_id`, `visibility`) SELECT `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, ccp.position + 10000 AS `position`, 0 AS `is_parent`, 1 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` FROM `catalog_category_entity` AS `cc`
INNER JOIN `catalog_category_entity` AS `cc2` ON cc2.path LIKE CONCAT(`cc`.`path`, '/%') AND cc.entity_id NOT IN (1)
INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc2.entity_id
INNER JOIN `catalog_product_website` AS `cpw` ON cpw.product_id = ccp.product_id
INNER JOIN `catalog_product_entity_int` AS `cpsd` ON cpsd.entity_id = ccp.product_id AND cpsd.store_id = 0 AND cpsd.attribute_id = 96
LEFT JOIN `catalog_product_entity_int` AS `cpss` ON cpss.entity_id = ccp.product_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 = ccp.product_id AND cpvd.store_id = 0 AND cpvd.attribute_id = 102
LEFT JOIN `catalog_product_entity_int` AS `cpvs` ON cpvs.entity_id = ccp.product_id AND cpvs.attribute_id = cpvd.attribute_id AND cpvs.store_id = 1
INNER JOIN `catalog_category_entity_int` AS `ccad` ON ccad.entity_id = cc.entity_id AND ccad.store_id = 0 AND ccad.attribute_id = 51
LEFT JOIN `catalog_category_entity_int` AS `ccas` ON ccas.entity_id = cc.entity_id AND ccas.attribute_id = ccad.attribute_id AND ccas.store_id = 1 WHERE (cpw.website_id = '1') AND (IFNULL(cpss.value, cpsd.value) = 1) AND (IFNULL(cpvs.value, cpvd.value) IN (4, 2)) AND (IFNULL(ccas.value, ccad.value) = 1) AND (`cc`.`entity_id` >= '1') AND (`cc`.`entity_id` < 501) 

which can take up to 30 minutes to complete. it's either that table or INSERT IGNORE INTO catalog_category_product_cat_tmp

We have about 80.000 categories, which might cause a problem, but surely we're not the only store in the world with that many.

When doing SHOW PROFILE in MySQL, it says it stuck on Sending data.

Has anyone had experience with this? Are there any indexes we could add to the tables to make this swifter? Any way to see which part of the query is the actual bottleneck?

Answer 1

no easy solution - ended up rewriting the indexer.