MySQL -添加多个派生表时的慢查询-优化

Question

对于我的查询，底部的两个派生表导致这个查询的速度非常慢。按原样执行查询大约需要45-55秒。现在，当我只删除其中一个派生表时(无论是哪个派生表)，查询时间降至0.1 - 0.3秒。我的问题；有多个派生表有问题吗？有没有更好的方法来执行这个？我的索引似乎都是正确的，我还将包括来自此查询的解释。

select t.name as team, u.name as "REP NAME", 
  count(distinct activity.id) as "TOTAL VISITS", 
  count(distinct activity.account_id) as "UNIQUE VISITS",
  count(distinct placement.id) as "COMMITMENTS ADDED",

  CASE WHEN 
    count(distinct activity.account_id) = 0 THEN (count(distinct 
    placement.id) / 1) 
    else (cast(count(distinct placement.id) as decimal(10,2)) / 
    cast(count(distinct activity.account_id) as decimal(10,2)))
  end as "UNIQUE VISIT TO COMMITMENT %",

  case when o.mode='basic' then count(distinct placement.id) else 
    count(distinct(case when placement.commitmentstatus='fullfilled' 
    then placement.id else 0 end)) 
  end as "COMMITMENTS FULFILLED",

  case when o.mode='basic' then 1 else 
    (CASE WHEN 
     count(distinct placement.id) = 0 THEN (count(distinct(case when 
     placement.commitmentstatus='fullfilled' then placement.id else 0 
    end)) / 1) 
    else (cast(count(distinct(case when 
    placement.commitmentstatus='fullfilled' then placement.id else 0 
    end)) as decimal(10,2)) / cast(count(distinct placement.id) as 
    decimal(10,2)))
    end) end as "COMMITMENT TO FULFILLMENT %"

from lpmysqldb.users u
left join lpmysqldb.teams t on t.team_id=u.team_id
left join lpmysqldb.organizations o on o.id=t.org_id
left join (select * from lpmysqldb.activity where 
  org_id='555b918ae4b07b6ac5050852' and completed_at>='2018-05-01' and 
  completed_at<='2018-06-01' and tag='visit' and accountname is not 
  null and (status='active' or status='true' or status='1')) as 
  activity on activity.user_id=u.id
left join (select * from lpmysqldb.placements where 
  orgid='555b918ae4b07b6ac5050852' and placementdate>='2018-05-01' and 
  placementdate<='2018-06-01' and (status IN ('1','active','true') or 
  status is null)) as placement on placement.userid=u.id

where u.org_id='555b918ae4b07b6ac5050852' 
  and (u.status='active' or u.status='true' or u.status='1')
  and istestuser!='1'
group by u.org_id, t.name, u.id, u.name, o.mode
order by count(distinct activity.id) desc

​

​

感谢您的帮助！

我已经编辑了下面的内容，将两个底部连接从对子查询的连接更改为直接对表的连接。仍然产生相同的结果。

​

​