Speed up mysql query that returns many results

Question

I am trying to speedup a mysql query that returns a lot of results. The idea is to show some results splitted by categories / weeks etc. But it takes for ever to load them. It is totally unusable.

Can you suggest a solution to that? What are my options when I have to deal with such situations?

SELECT  DISTINCT vowao.id_product_attribute, WEEKOFYEAR(vowao.date_purchased) AS week,
        YEAR(vowao.date_purchased) AS year, 
      ( SELECT  COUNT(*)
            FROM  ps_view_orders_w_attributes_ordered vowao_s
            WHERE  WEEKOFYEAR(vowao_s.date_purchased) = WEEKOFYEAR(vowao.date_purchased)
              AND  vowao_s.id_product_attribute = vowao.id_product_attribute
      ) AS amount_sold,
      ( SELECT  al.public_name
            FROM  ps_attribute_lang al
            WHERE  al.id_attribute = vpac.id_attribute
              AND  al.id_lang='2'
      ) AS name, 
      ( SELECT  al.id_attribute
            FROM  ps_attribute_lang al
            WHERE  al.id_attribute = vpac.id_attribute
              AND  al.id_lang='2'
      ) AS id_attribute
    FROM  `ps_view_orders_w_attributes_ordered` vowao
    JOIN  `ps_view_product_attribute_combination` vpac
            ON vpac.id_product_attribute = vowao.id_product_attribute
    WHERE  vowao.id_shop = '".$id_shop."'
      AND  vpac.is_color_group = 1
      AND  WEEKOFYEAR(vowao.date_purchased) IS NOT NULL
      AND  YEAR(vowao.date_purchased) = 2015

Answer 1

al needs INDEX(id_lang, id_attribute) (or the opposite order)

Key-value (EAV) schemas are notoriously clumsy and inefficient.

AND YEAR(vowao.date_purchased) = 2015 hides a potentially indexed column inside a function, thereby eliminating the possibility of using an index. Instead, do

AND vowao.date_purchased >= '2015-01-01
AND vowao.date_purchased <  '2015-01-01 + INTERVAL 1 YEAR

Get rid of this, since the above query will fail for NULL:

AND  WEEKOFYEAR(vowao.date_purchased) IS NOT NULL

Once you have done those, add

INDEX(id_shop, date_purchased)

Please provide SHOW CREATE TABLE for each table, so we won't have to guess at what is wrong.

Answer 2

I took a crack at it. Here were my goals:

1. No correlated subqueries
2. No DISTINCT (probably what slowed you down the most)
3. No unnecessary formulas in the constraints
4. All standard SQL (because I'm not that familiar with MySQL syntax!)

Here's the query:

SELECT 
    vowao.id_product_attribute, 
    WEEKOFYEAR(vowao.date_purchased) AS week, 
    YEAR(vowao.date_purchased) AS year,
    amount_group.amount_sold,
    al.public_name AS name,
    al.id_attribute AS id_attribute
FROM 
    `ps_view_orders_w_attributes_ordered` vowao
JOIN 
    `ps_view_product_attribute_combination` vpac 
    ON 
    vpac.id_product_attribute = vowao.id_product_attribute
LEFT JOIN
     ps_attribute_lang al 
     ON 
     al.id_attribute = vpac.id_attribute 
     AND 
     al.id_lang='2'
LEFT JOIN
    (SELECT 
        WEEKOFYEAR(date_purchased) as week_of_year,
        id_product_attribute,
        COUNT(*) as amount_sold
    FROM
        ps_view_orders_w_attributes_ordered
    GROUP BY
        WEEKOFYEAR(date_purchased), 
        id_product_attribute
    ) amount_group
    ON
    WEEKOFYEAR(vowao.date_purchased)  = amount_group.week_of_year
    AND
    vowao.id_product_attribute = amount_group.id_product_attribute
WHERE 
    vowao.id_shop = '".$id_shop."'
    AND 
    vpac.is_color_group = 1
    AND 
    vowao.date_purchased IS NOT NULL
    AND 
    YEAR(vowao.date_purchased) = 2015
GROUP BY
    vowao.id_product_attribute,
    WEEKOFYEAR(vowao.date_purchased),
    YEAR(vowao.date_purchased),
    al.public_name,
    al.id_attribute

Answer 3

Ignoring the aggregation part for now, something like this will be faster...

 SELECT DISTINCT v.id_product_attribute
               , DATE_FORMAT(v.date_purchased,'%v-%x') yearweek
               , al.public_name
               , al.id_attribute
            FROM ps_view_orders_w_attributes_ordered v
            JOIN ps_view_product_attribute_combination vpac 
              ON vpac.id_product_attribute = v.id_product_attribute
            JOIN ps_attribute_lang al 
              ON al.id_lang = vpac.id_attribute
           WHERE v.id_shop = '$id_shop'
             AND vpac.is_color_group = 1
             AND v.date_purchased BETWEEN '2015-01-01' AND '2015-12-31'
             AND v.date_purchased IS NOT NULL
             AND al.id_attribute = 2;

The next step would be to review the EXPLAIN, and add indexes as appropriate.

Answer 4

One thing you can try is to convert your correlated subqueries into joins:

SELECT DISTINCT
    vowao.id_product_attribute,
    WEEKOFYEAR(vowao.date_purchased) AS week,
    YEAR(vowao.date_purchased) AS year,
    amounts_sold.total,
    attr_names.id_attribute,
    attr_names.public_name AS name,
FROM `ps_view_orders_w_attributes_ordered` vowao
JOIN `ps_view_product_attribute_combination` vpac ON vpac.id_product_attribute = vowao.id_product_attribute
LEFT JOIN (
    SELECT WEEKOFYEAR(vowao_s.date_purchased) AS s_week, vowao_s.id_product_attribute AS s_attr, COUNT(*) AS total
    FROM ps_view_orders_w_attributes_ordered vowao_s
    GROUP BY WEEKOFYEAR(vowao_s.date_purchased), vowao_s.id_product_attribute
) amounts_sold ON amounts_sold.s_week = WEEKOFYEAR(vowao.date_purchased) AND amounts_sold.s_attr = vowao.id_product_attribute
LEFT JOIN (
    SELECT al.id_attribute, al.public_name
    FROM ps_attribute_lang al
    WHERE al.id_lang='2'
) attr_names ON al.id_attribute = vpac.id_attribute
WHERE vowao.id_shop = '".$id_shop."'
  AND vpac.is_color_group = 1
  AND WEEKOFYEAR(vowao.date_purchased) IS NOT NULL
  AND YEAR(vowao.date_purchased) = 2015

For better/more help, show the results of using EXPLAIN on your query. You can also create a SQL Fiddle with the relevant schema and sample data to help helpers.