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);
The 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!