建议强制使用date索引是还是 from data_log force index(call_date)
先检查你的这个子查询效率 select cam_id,DATE_FORMAT(call_date,'%Y-%m-%d') as call_date ,sum(case when status='A' then 1 else 0 end ) as 'A' ,sum(case when status='B' then 1 else 0 end ) as 'B' ,sum(case when status='C' then 1 else 0 end ) as 'C' ,sum(case when status='D' then 1 else 0 end ) as 'D' from data_log where call_date between '2012-11-14 00:00:00' and '2012-11-17 23:59:59' group by DATE_FORMAT(call_date,'%Y-%m-%d'),cam_id order by null建议把 order by null 去掉。 另外尽可能直接在这一个查询中实现,不要使用子查询。
from data_log force index(call_date)
select cam_id,DATE_FORMAT(call_date,'%Y-%m-%d') as call_date
,sum(case when status='A' then 1 else 0 end ) as 'A'
,sum(case when status='B' then 1 else 0 end ) as 'B'
,sum(case when status='C' then 1 else 0 end ) as 'C'
,sum(case when status='D' then 1 else 0 end ) as 'D'
from data_log
where call_date between '2012-11-14 00:00:00' and '2012-11-17 23:59:59'
group by DATE_FORMAT(call_date,'%Y-%m-%d'),cam_id order by null建议把 order by null 去掉。
另外尽可能直接在这一个查询中实现,不要使用子查询。
直接force index(call_date) 不太合适,因为 where 还有其他条件。不使用子查询的话,像这种列数不固定的 求比率之类 改怎么写啊?!@!