mysql - SQL子查询优化多个聚合操作

Question

我正在尝试按项目查找不同表和组的平均值和总和。我还想将返回的表压缩为一行。所以对于我的子查询我得到这个结果

使用外部查询我期望这样:

这是我到目前为止的 sql 代码。它可以工作，但它的性能非常慢，我不知道为什么或如何优化它。

select sum(sub.count) as count, avg(sub.opened) as opened,
avg(sub.clicked) as clicked, avg(sub.started_watching) as started_watching,
sum(sub.views) as views
from (
select p.id, count(e.id) as count, 
avg(e.opened) as opened, avg(e.read_email) as clicked,
avg(e.started_video) as started_watching, sum(e.views) as views
from projects p
inner join guests g
on g.project_id = p.id
inner join videos v
on v.guest_id = g.id
inner join emails e
on e.video_id=v.id
group by p.id) sub; 

Answer 1

查看您的查询，主要问题与子查询结果的创建有关
因此，改进此查询很重要，例如在每个表的联接涉及的列上添加适当的索引adn 最终为 select 中使用的列添加复合索引(在 join 中使用的列之后)

  Select  sum(sub.count) as count
        , avg(sub.opened) as opened
        , avg(sub.clicked) as clicked
        , avg(sub.started_watching) as started_watching
        , sum(sub.views) as views
  from (
    select p.id
        , count(e.id) as count
        , avg(e.opened) as opened
        , avg(e.read_email) as clicked
        , avg(e.started_video) as started_watching
        , sum(e.views) as views
    from projects p
    inner join guests g on g.project_id = p.id
    inner join videos v on v.guest_id = g.id
    inner join emails e on e.video_id=v.id
    group by p.id
  ) sub; 

所以请确保您有正确的索引

guests  (project_id)
videos  (guest_id)
emails  (video_id, id, opened, read_email,started_video, views)

and obvious on projects (id)