无子查询的MySql查询优化

问题描述：

任何人都可以帮助我优化此查询吗？无子查询的MySql查询优化

SELECT distinct t.designation, 
     (SELECT sum(time_to_sec(timediff(outtime,intime))) 
     FROM onlinetime where datecreated BETWEEN '2012-01-01 00:00:01' 
     AND '2012-12-31 23:59:59' and designation=t.designation) as totsec 
FROM onlinetime t;

我想从onlinetime表中获取指定和秒数的总和。它只有8000条记录，但上面的查询花费很多时间来执行。有没有其他的方法来优化这个？

答

为什么你需要加入表时，你可以直接得到它们的总摘要各个指定

SELECT designation, sum(time_to_sec(timediff(outtime,intime))) totsec 
FROM onlinetime 
WHERE datecreated BETWEEN '2012-01-01 00:00:01' AND '2012-12-31 23:59:59' 
GROUP BY designation

只是datecreated列中添加索引更快的性能。

ALTER TABLE onlinetime ADD INDEX (datecreated)

或者也许你想这个，

SELECT a.designation, 
     COALESCE(sum(time_to_sec(timediff(b.outtime,b.intime))), 0) totsec 
FROM onlinetime a 
     LEFT JOIN onlinetime b 
      ON a.designation = b.designation AND 
       b.datecreated BETWEEN '2012-01-01 00:00:01' AND '2012-12-31 23:59:59' 
GROUP BY a.designation

这就是我想要的...谢谢你的时间... – user1854007 2013-02-25 16:04:17

不客气'：D' – 2013-02-25 16:06:17

答

正确的答案是：

SELECT t.designation, 
     sum(case when datecreated between '2012-01-01 00:00:01' AND '2012-12-31 23:59:59' 
       then sum(time_to_sec(timediff(outtime,intime) 
      end) as totsec 
from onlinetime t 
group by t.designation

把过滤的where子句中忽略了原始查询的意图，这是将所有“指定”放在表格中。

无子查询的MySql查询优化

相关推荐