mysql - date_format(from_unixtime()) 子选择非常慢

Question

我尝试使用以下查询来吐出日期范围内的每一天，并显示潜在客户、分配和返回的数量:

select 
date_format(from_unixtime(date_created), '%m/%d/%Y') as date_format, 
(select count(distinct(id_lead)) from lead_history where (date_format(from_unixtime(date_created), '%m/%d/%Y') = date_format) and (id_vertical in (2)) and (id_website in (3,8))) as leads,
(select count(id) from assignments where deleted=0 and (date_format(from_unixtime(date_assigned), '%m/%d/%Y') = date_format) and (id_vertical in (2)) and (id_website in (3,8))) as assignments,
(select count(id) from assignments where deleted=1 and (date_format(from_unixtime(date_deleted), '%m/%d/%Y') = date_format) and (id_vertical in (2)) and (id_website in (3,8))) as returns 
from lead_history 
where date_created between 1509494400 and 1512086399 
group by date_format

date_created、date_assigned 和 date_deleted 字段是表示时间戳的整数。 id、id_lead、id_vertical 和 id_website 已编入索引。

向 date_created、date_assigned、date_deleted 和 deleted 添加索引是否有助于加快速度？我遇到的问题是它非常慢，并且我不确定使用 date_format(from_unixtime(...

) 时索引是否有帮助

这是解释:

Answer 1

查看您的代码，您可以将查询重写为 ..

  select 
      date_format(from_unixtime(date_created), '%m/%d/%Y') as date_format
      , count(distinct(h.id_lead) as leads
      , sum(case a.deleted = 1 then 1 else 0 end) assignments
      , sum(case b.deleted = 0 then 1 else 0 end) returns
  from lead_history  h 
  inner join assignments on  a a.date_assigned = h.date_created 
        and a.id_vertical = 2 
        and id_website in (3,8))
  inner join assignments on  b b.deleted = h.date_created 
        and a.id_vertical = 2 
        and id_website in (3,8))
  where date_created between 1509494400 and 1512086399 
  group by date_format

无论如何，您应该避免无用的 () 和嵌套 ()，避免日期之间无用的转换并使用 join 而不是 subselect ..或至少使用 case 减少类似的 sabuselect

PS 对于什么关注索引记住，对列值使用转换无效使用相关索引..