I have performance problem with this query:
SELECT CONCAT(s.store_id, '-', p.entity_id, IFNULL(CONCAT('-', l.product_id), '')) AS store_product_parent_id, p.entity_id, s.store_id, l.product_id AS parent_id, p.type_id, IFNULL(sc.pcs_sold_in_period, 0) AS pcs_sold_in_period, IFNULL(translation.value, 0) AS translation FROM catalog_product_entity AS p LEFT JOIN catalog_product_link AS l ON (l.link_type_id = 3) AND (l.linked_product_id = p.entity_id) LEFT JOIN sc ON sc.product_id = p.entity_id OR sc.parent_id = p.entity_id INNER JOIN core_store AS s ON (s.store_id != 1) AND (s.is_active) INNER JOIN eav_attribute AS a ON (a.attribute_code = CONCAT('translation_', s.code)) LEFT JOIN catalog_product_entity_int AS translation ON (translation.entity_id = p.entity_id) AND (translation.attribute_id = a.attribute_id) AND (translation.store_id = 0);
sc identifier is view created by this query:
SELECT l.product_id AS parent_id, oi.product_id AS product_id, sum((oi.qty_ordered - oi.qty_canceled)) AS pcs_sold_in_period FROM sales_flat_order_item oi JOIN sales_flat_order o ON (o.entity_id = oi.order_id) AND (o.state <> 'canceled') AND (o.created_at > (now() - interval 30 day)) LEFT JOIN catalog_product_link l ON (l.link_type_id = 3) AND (l.linked_product_id = oi.product_id) GROUP BY l.product_id, oi.product_id WITH rollup HAVING (parent_id IS NOT NULL) xor (oi.product_id IS NOT NULL);
There are about 33k products and just select (1st query) performance is OK. Problem is when I add
ORDER BY clause to the first query to sort the result by any column. It tooks about 45 seconds now and it slows down as there are more rows in result of 2nd query (view).
Description of queries:
The first selects products in each store with one attribute with code based on store code.
The second selects pcs sold count for simple products and for grouped products by by summing count of all their childs (done by
GROUP BY WITH ROLLUP).
How to fix that problem? Thanks!