mysql - MySQL：优化和/或不同于派生表
问题描述
在使用几个非常大的数据集时，我正在尝试优化我的查询。我当前的查询需要一些时间来处理，即使只有几天的数据，而这将是为了提取每月数据。
我的问题是最好的方法是什么：（我在这个例子中使用了一个数据集，但请记住，将有三个具有基本相同的结构，都在同一个查询中）
select u.id, u.name,
count(distinct(case when sales.tag='event' then sales.id end)) as
eventsales,
count(distinct(case when sales.tag='onpremise' then sales.id end))
as onpresales,
count(distinct(case when sales.tag='offpremsales' then sales.id
end)) as offpresales,
count(distinct(case when sales.fulfillment='yes' and
sales.premise='on' then sales.id end)) as fullonsales,
count(distinct(case when sales.fulfillment='no' and
sales.premise='on' then sales.id end)) as fulloffsales
from users u
left join (
select * from sales where org='XXXX' and invoicedate BEWTEEN '2018-
04-01' and '2018-04-10' and status='active'
) sales on sales.user=u.id
where u.status='active'
group by u.org, u.id, u.name
order by u.team
我不是专业人士，仍在学习，但这是最佳表现吗？
放弃派生表并使用 5 个子查询会更好吗？由于每个只有几个细微的变化，我应该创建多个派生表吗？
此外，我在此示例中使用的索引是 sales 表：org、invoicedate、status 但是，根据我的研究，MySQL 不会在派生表上使用索引。这是准确的吗？
在此先感谢，如果我需要提供任何其他信息，请告诉我。 下面的整个实际查询
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",
ROUND((select sum(s.volumece) from lpmysqldb.sales s where
s.org_id='555b918ae4b07b6ac5050852' and s.account_id IN (select
account_id from lpmysqldb.activity where
org_id='555b918ae4b07b6ac5050852' and user_id=u.id and
(completed_at between '2018-04-01' and '2018-04-04') and
tag='visit' and accountname is not null and (status='active' or
status='true' or status='1')) and (s.invoice_date between
DATE_FORMAT(CURDATE(), '%Y-01-01') and DATE_FORMAT(CURDATE(), '%Y-
%m-%d'))),2) as "CURRENT YEAR VOLUME",
ROUND((select sum(s.volumece) from lpmysqldb.sales s where
s.org_id='555b918ae4b07b6ac5050852' and s.account_id IN (select
account_id from lpmysqldb.activity where
org_id='555b918ae4b07b6ac5050852' and user_id=u.id and
(completed_at between '2018-04-01' and '2018-04-04') and
tag='visit' and accountname is not null and (status='active' or
status='true' or status='1')) and (s.invoice_date between
(DATE_FORMAT(CURDATE(), '%Y-01-01') - INTERVAL 1 YEAR) and
(DATE_FORMAT(CURDATE(), '%Y-%m-%d') - INTERVAL 1 YEAR))),2) as
"PREVIOUS YEAR VOLUME",
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 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 end)) /
1)
else (cast(count(distinct(case when
placement.commitmentstatus='fullfilled' then placement.id end))
as decimal(10,2)) / cast(count(distinct placement.id) as
decimal(10,2)))
end)
END as "COMMITMENT TO FULFILLMENT %",
CASE WHEN o.mode='basic' then count(distinct placement.id) else
count(distinct(case when placement.commitmentstatus='fullfilled'
AND (premise = 1 or premise IS NULL) then placement.id end))
END as "ON PREM COMMITMENTS FULFILLED",
CASE WHEN o.mode='basic' then count(distinct placement.id) else
count(distinct(case when placement.commitmentstatus='fullfilled'
AND premise = 0 then placement.id end))
END
CASE WHEN o.mode='basic' then count(distinct placement.id) else
count(distinct(case when placement.commitmentstatus='fullfilled'
AND ispackage IN ('1','true','active') then placement.id end))
END as "PACKAGE COMMITMENTS FULFILLED",
CASE WHEN o.mode='basic' then count(distinct placement.id) else
count(distinct(case when placement.commitmentstatus='fullfilled'
AND isdraft IN ('1','true','active') then placement.id end))
END as "DRAFT COMMITMENTS FULFILLED",
(select count(distinct id) from lpmysqldb.activity where
org_id='555b918ae4b07b6ac5050852' and user_id=u.id and
(completed_at between '2018-04-01' and '2018-04-04') and
activity_name IN ('Display','Floor Display') and (activity.status
IN ('1','active','true','') OR activity.status IS NULL)) as
"DISPLAYS BUILT",
(select count(distinct id) from lpmysqldb.activity where
org_id='555b918ae4b07b6ac5050852' and user_id=u.id and
(completed_at between '2018-04-01' and '2018-04-04') and
tag='event' and (activity.status IN ('1','active','true','') OR
activity.status IS NULL)) as "EVENTS"
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 between '2018-
04-01' and '2018-04-04') and tag='visit' and accountname is not
null and (status IN ('1','active','true','') OR status IS NULL))
activity on activity.user_id=u.id
left join (select * from lpmysqldb.placements where
orgid='555b918ae4b07b6ac5050852' and (placementdate between '2018-
04-01' and '2018-04-04') and (status IN ('1','active','true','') OR
status IS NULL)) placement on placement.userid=u.id
where u.org_id='555b918ae4b07b6ac5050852'
and u.status IN ('active','true','1')
and istestuser!='1'
group by u.org_id, t.name, u.id, u.name, o.mode
order by t.name asc, count(distinct activity.id) desc