SQL (sub)query performance

Question

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!

Answer 1

Problem

It is a pity, but there is no simple solution for your query. There are multiple basic issues exists in your original queries, that require changing your approach in general:

1. As more joins you have a higher chance, your MySQL query will create intermediate temporary tables to join them.
2. The amount of LEFT JOIN statements only adds performance degradation, as it prohibits MySQL to use indexes on next INNER JOIN statements.
3. The view you've created is good only for retrieving data, but when you LEFT JOIN it, it does not have any real column index to serve.
4. The initial performance of the original query is good only because data gets stored into query cache. Execute it without query cache by using SQL_NO_CACHE flag to see the issue.
5. ORDER BY on queries that do not have immediate column index for it, only makes things worse, as MySQL needs to materialize the whole data-set on to the disc, walk over it and sort by column value.
6. Such dynamic INNER JOIN condition on attribute code does not work well: (a.attribute_code = CONCAT('translation_', s.code)). MySQL required to evaluate the statement for every attribute table entry multiplied by store entry, so not only indexes are not used, but also other columns search performance gets degradation.
7. You do not need GROUP BY ... WITH ROLLUP in your view query, is not the most performant way to generate your data. Better to separate into two queries with materialized tables.
8. Left joining catalog_product_link on the main query multiplies your 33,000 product database select by the number of simple products assigned into grouped. I expect you have at least 1/3 of products assigned to grouped, will result in 363,000,000 records for MySQL to operate.

Solution

You need to materialize such parts of your query by creating index tables with the indexer that populates it:

1. Translation Attribute Index. You will use this table as a base for your query, so translation column might should NULL values for products that do not have it filled in. You will need to create table with such columns:

   • product_idPart of combined primary key
   • store_id Part of combined primary key
   • translation Represents value of translation_[store_code] attribute. I recommend using separate queries for generation of data for each store view, so NO dynamic conditions are used. The column should be in regular btree index.

2. 30-Day Sold Qty Index. You will use it as a replacement of your JOIN to view and catalog_product_link table. Table can be generated without using any catalog_product_link tables as order item already contains parent_item_id column. Table will contain such columns:

   • product_idPart of combined primary key, taken from order_item.product_id
   • parent_idPart of combined primary key, can be 0, taken from parent_order_item.product_id
   • type_id indexed column that is taken from order_item table product type.
   • sold_qty indexed column that is aggregated between from parent_order_item.qty_ordered*order_item.qty_ordered when there is a parent or just aggregation of order_item.qty_ordered

Then your query will simply join 2 tables that are indexed and ready to perform a fast data lookup.

The overall generation of the whole index tables (if implemented properly) will not take more than 1-2 seconds on your database size.

You might also need some additional indexes, as from your question I don't have a proper understanding of your report requirements