Need to speed up MySQL Subquery

Hey I have a MySQL query that uses a nested subquery.

I have tried many ways to speed it up as it takes about 2 seconds to run and is slowing down a webp开发者_如何学编程age.

How can i speed up this query? I have already tried using views and query caching but performance benefits were nominal.

SELECT w.WID, 
              max(wb.BlockPrice) as highestPrice, 
              min(wb.BlockPrice) as lowestPrice, 
              max(bi.Impressions) as highestImpressions, 
              min(bi.Impressions) as lowestImpressions
                        FROM Website w
                        JOIN Website_Block wb on wb.WID = w.WID
                    JOIN Website_Block_Impressions wbi on wbi.WBID = wb.WBID and wbi.StatDate > DATE_SUB(NOW(),INTERVAL 1 DAY)
                JOIN (
                SELECT round((Sum(Impressions) / Count(impDate)) * 30) AS Impressions, WID as WIDImpressions
                  FROM (SELECT COUNT(wbi.WBIID) AS Impressions,
                              CAST(wbi.StatDate AS DATE) AS impDate,
                              wbi.WBID,
                              wb.WID
                          FROM Website_Block_Impressions wbi
                          JOIN Website_Block wb ON wb.WBID = wbi.WBID
                          WHERE wb.BlockEnabled = 1 
                          AND wb.Archived = 0
                          AND `wbi`.StatDate > DATE_ADD(now(), INTERVAL -wb.BlockDuration DAY)
                          GROUP BY CAST(wbi.StatDate AS DATE), wbi.WBID) AS impressions
                  GROUP BY WBID) as bi
                        WHERE w.Archived = 0
                        AND w.Approved = 1
                        AND bi.WIDImpressions = w.WID
                        AND bi.Impressions between 0 AND 73000
                        GROUP BY w.WID
            LIMIT 0,10

Any help would be appreciated.

You'll want to add indexes on the WHERE criteria. For example, add an index containing the fields w.Archived and w.Approved. Without indices like that, MySQL will have to scan every single row first, instead of knowing exactly which rows it should be working with.

But like The Scrum Meister mentioned, we'll need to see the EXPLAIN output and table definitions to have a better idea of where the bottlenecks are and how to address them.

If you can't there are plenty of resources on the net that will help you understand how to create optimal indexes for increased query speed. This slideshow gives a basic overview of where to start: http://www.slideshare.net/manikandakumar/mysql-query-and-index-tuning

You might be able to reduce this to a single subquery if you think about what you are selecting. As you are SUMing a COUNT, and COUNTing something you are GROUPing by. You could do the same by COUNTing everything in one go, and COUNTing DISTINCT values the other part.

SELECT
    w.WID, 
    max(wb.BlockPrice) as highestPrice, 
    min(wb.BlockPrice) as lowestPrice, 
    max(bi.Impressions) as highestImpressions, 
    min(bi.Impressions) as lowestImpressions
FROM
    Website w
    JOIN Website_Block wb on wb.WID = w.WID
    JOIN Website_Block_Impressions wbi on wbi.WBID = wb.WBID and wbi.StatDate > DATE_SUB(NOW(),INTERVAL 1 DAY)
    JOIN (
        SELECT
            ROUND((SUM(wbi.WBIID) / COUNT(DISTINCT DATE(wbi.StatDate))) * 30) AS Impressions
            wb.WID
        FROM
            Website_Block_Impressions wbi
            JOIN Website_Block wb ON wb.WBID = wbi.WBID
        WHERE
            wb.BlockEnabled = 1 
            AND wb.Archived = 0
            AND `wbi`.StatDate > DATE_ADD(now(), INTERVAL -wb.BlockDuration DAY)
        GROUP BY
            wbi.WBID
    ) bi
WHERE
    w.Archived = 0
    AND w.Approved = 1
    AND bi.WIDImpressions = w.WID
    AND bi.Impressions between 0 AND 73000
GROUP BY
    w.WID
LIMIT 0,10

You shouldn't need to CAST a date-type. If it isn't a date-column, something is probably wrong there. CASTing meens no index can be used.

Furthermore, take into consideration the easiest way for mySQL to use indexes is to make a COMBINED index for all columns you are WHEREing, GROUPing or JOINing on for each table.

So something like:

• Website [WID, Archived, Approved]
• Website_Block [WBID, WID, BlockEnabled, Archived]
• Website_Block_Impressions [WBIID, WBID, StatDate]