cancel
Showing results for 
Search instead for 
Did you mean: 

Saving products is very slow

Saving products is very slow

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?

 

2 REPLIES 2

Re: Saving products is very slow

You need to optimize your database server parameters (my.cnf)

Look on binary log cache options, buffer pools sizes etc

Re: Saving products is very slow

This is not a kind of query that I expect from a product wants to be called top of its class.

These kind of queries block many tables and affects real use. For me its a bad practice, seems like sql autogenerated from a tool, not a human with good knowledge of databases.