Multiple dependent subqueries

This query currently takes approximately 250 seconds to run and I'd like to reduce it by as much as I can. I have tried using left joins instead of subqueries and it increases run time.

``
SELECT items.id,

ifnull((SELECT sum(history.quantity) FROM history where history.itemID = items.id and history.date > date_sub(now(), interval 7 DAY) group by itemID), '0') as sold7,
ifnull((SELECT round(supply) FROM supply WHERE itemID = items.id AND date > date_sub(now(), interval 7 DAY) group by itemID), '0') as listed7,

ifnull((SELECT sum(history.quantity) FROM history where history.itemID = items.id and history.date > date_sub(now(), interval 30 DAY) group by itemID), '0') as sold30,
ifnull((SELECT round(supply) FROM supply WHERE itemID = items.id AND date > date_sub(now(), interval 30 DAY) group by itemID), '0') as listed30,

ifnull((SELECT sum(history.quantity) FROM history where history.itemID = items.id and history.date > date_sub(now(), interval 60 DAY) group by itemID), '0') as sold60,
ifnull((SELECT round(supply) FROM supply WHERE itemID = items.id AND date > date_sub(now(), interval 60 DAY) group by itemID), '0') as listed60,

ifnull((SELECT sum(history.quantity) FROM history where history.itemID = items.id and history.date > date_sub(now(), interval 90 DAY) group by itemID), '0') as sold90,
ifnull((SELECT round(supply) FROM supply WHERE itemID = items.id AND date > date_sub(now(), interval 90 DAY) group by itemID), '0') as listed90,

ifnull((SELECT sum(history.quantity) FROM history where history.itemID = items.id and history.date > date_sub(now(), interval 180 DAY) group by itemID), '0') as sold180,
ifnull((SELECT round(supply) FROM supply WHERE itemID = items.id AND date > date_sub(now(), interval 180 DAY) group by itemID), '0') as listed180

FROM items

group by item

Formatting

First, the way you wrote this makes it difficult to understand what you are trying to do. Here are a few points to improve it:

• Use indentation and line breaks that illustrates your nesting. I provided an example below.

• Use all caps for keywords (or all lower case, just be consistent).

• Back ticks are not required unless you have a space or reserved operator in your code. It makes the code easier to follow without them.

Here is an example, this:

ifnull((SELECT sum(`history`.`quantity`) FROM `history` where `history`.`itemID` = `items`.`id` and `history`.`date` > date_sub(now(), interval 7 DAY) group by `itemID`), '0') as `sold7`,

Could be:

IFNULL(
    (SELECT SUM(history.quantity) 
    FROM history 
    WHERE history.itemID = items.id
    AND history.date > DATE_SUB(NOW(), INTERVAL 7 DAY) 
    GROUP BY itemID
    ), 0
) AS sold7,

Performance

The first problem I see is the excessive use of IFNULL() and subqueries within them. I'm not sure as to why you are replacing NULL with 0 as your aggregate functions will factor NULLs out anyways. But I suppose you have a reason to do this that I don't understand.

I have at least three recommendations:

• If your dataset is very large, you may want to break it down into smaller steps using a temporary table.

• You would likely want to use a LEFT JOIN instead of subqueries.

• I would strongly suggest to create a stored procedure as this looks like the type of query that is run regularly. That way the execution plan will saved with your query.

Here you go:

DELIMITER $
    CREATE OR REPLACE PROCEDURE proc_MyProcedure() AS -- pick a meaningful name
    BEGIN
    INSERT INTO TEMPORARY TABLE temp_MyTable -- pick a meaningful name
     SELECT items.id AS itemID,
      history.quantity AS historyQty,
      history.date AS historyDate,
      supply.supply AS supplyQty,
      supply.date AS supplyDate
     FROM items 
     LEFT JOIN supply ON items.id = supply.itemID
     LEFT JOIN history ON items.id = history.itemID
     WHERE history.date > DATE_SUB(NOW(), INTERVAL 180 DAY)
     OR supply.date  > DATE_SUB(NOW(), INTERVAL 180 DAY)
    ;
    -- here we set nulls to 0
    UPDATE temp_MyTable
    SET historyQty = 0
    WHERE historyQty IS NULL;
    UPDATE temp_MyTable
    SET supplyQty = 0
    WHERE supplyQty IS NULL;
    -- finally we aggregate on the temp table
    SELECT  (SELECT SUM(historyQty) FROM temp_Mytable WHERE history.date > DATE_SUB(NOW(), INTERVAL 7 DAY)) AS sold7,
     (SELECT ROUND(supply) FROM temp_MyTable WHERE supplyDate > DATE_SUB(NOW(), INTERVAL 7 DAY)) AS listed7,
    -- etc.
    FROM temp_MyTable
    GROUP BY itemID;
    END$
DELIMITER ;

Then just CALL proc_MyProcedure(). You will notice the first time takes longer, but any subsequent calls should be much faster.

StackExchange Code Review Q#57602, answer score: 3