MySQL 优化 avg、sum 查询有 left join 嵌套 select

Question

是否要针对 MySQL 5.1 优化此查询？

我有“Calls”表，有字段“Caller”(从号码)，“Callee”(到号码)和“Duration”，这个查询在SQLite中工作正常，但在MySQL中非常慢，数据量为140亿记录一下。

我在“调用者”字段上有索引。

select A.Caller, ifnull(aCallOut, 0) as CallOut, round((ifnull(aDuration, 0) / 60), 2) as Duration, round((ifnull(aAverage, 0) / 60), 2) as Average, Times, 0, 0, 0, 0
from
  (
    select Caller, sum(duration) as aDuration, count(*) Times -- sum of duration of each caller
    from Calls
    group by Caller
  ) As A

left join
  (
    select Caller, avg(duration) as aAverage -- i want avg duration of numbers that calling more than 0 seconds
    from Calls
    where Duration > 0
    group by Caller
  ) as C
  on A.Caller = C.Caller

left join
  (
    select Caller, count(*) as aCallOut -- i need to count how many numbers that caller called, ignoring the duplicating calls
    from
      (
        select Caller, count(*) aCount
        from Calls
        group by Caller, Callee
      ) as D
    group by Caller
  ) as B
  on A.Caller = B.Caller

Answer 1

您可以通过条件聚合来完成所有这些:

select Caller, sum(duration) as aDuration, count(*) as Times,
       avg(case when duration > 0 then duration end) as aAverage,
       count(distinct Callee) as aCallOut
from Calls
group by Caller;

小提示:如果 Callee 为 NULL，则 aCallOut 可能会减少 1。