怎样才能使这个MySQL查询更高效？子查询/左连接

Question

我想找人帮助我更快地进行查询-

SELECT DISTINCT waits.ride, 
                T2.daywait, 
                T3.timewait, 
                T4.daytimewait, 
                T5.currentwait 
FROM   waits 
       left join (SELECT Avg(waittime) AS dayWait, 
                         ride 
                  FROM   waits 
                  WHERE  dayofweek = '" . $dow . "' 
                  GROUP  BY ride) AS T2 
              ON T2.ride = waits.ride 
       left join (SELECT Avg(waittime) AS timeWait, 
                         ride 
                  FROM   waits 
                  WHERE  currenttime >= '" . $minusTime . "' 
                         AND currenttime <= '" . $plusTime . "' 
                  GROUP  BY ride) AS T3 
              ON T3.ride = waits.ride 
       left join (SELECT Avg(waittime) AS dayTimeWait, 
                         ride 
                  FROM   waits 
                  WHERE  currenttime >= '" . $minusTime . "' 
                         AND currenttime <= '" . $plusTime . "' 
                         AND dayofweek = '" . $dow . "' 
                  GROUP  BY ride) AS T4 
              ON T4.ride = waits.ride 
       left join (SELECT waittime AS currentWait, 
                         ride 
                  FROM   waits 
                  GROUP  BY ride 
                  ORDER  BY wid DESC) AS T5 
              ON T5.ride = waits.ride 
WHERE  park = '" . getPark() . "'

http://pastebin.com/DDSpGFWQ

为了让我们对这个查询的应用有个大概的了解，我使用它来填充这个链接- http://www.wdwhelper.com上的公园。如果你需要更多信息，请让我知道。

我也在寻找对子查询T5的具体帮助-我想获得每次骑行的最新(最后添加到数据库) waitTime -有什么想法这将是可能的？谢谢!

Answer 1

只需执行一个子查询，而不是四个，并读取表一次而不是四次，

您将节省3/4 (75%)的时间：

SELECT ride , 
   Avg( CASE WHEN dayofweek = '" . $dow . "' THEN waittime END) AS dayWait,
   Avg( CASE WHEN currenttime >= '" . $minusTime . "' 
                  AND currenttime <= '" . $plusTime . "' 
        THEN waittime END) AS dayWait,  
   Avg( CASE WHEN currenttime >= '" . $minusTime . "' 
                  AND currenttime <= '" . $plusTime . "' 
                  AND dayofweek = '" . $dow . "' 
        THEN waittime END) AS dayWait,
    waittime AS currentWait                     
FROM   waits 
GROUP  BY ride