如何优化SQL查询日期字段

Question

嗨，我有大量的数据，我想执行SQL查询从服务器获取数据。它需要很多时间来检索数据。请建议我如何优化这个查询。下面是我的代码:

SELECT count(*) as count,
       DATE_FORMAT(date,'%d-%b-%y') as dateName 
from myTable 
where date BETWEEN UTC_TIMESTAMP() - INTERVAL 30 DAY AND UTC_TIMESTAMP() 
and id=123 
GROUP BY DAY(date) 
order by date ASC  

Answer 1

SELECT count(*) as count,
       DATE_FORMAT(date,'%d-%b-%y') as dateName 
from myTable, (select UTC_TIMESTAMP() - INTERVAL 30 DAY as start_date, UTC_TIMESTAMP() as end_date) dd  
where date BETWEEN dd.start_date AND dd.end_date
and id=123 
GROUP BY DAY(date) 
order by date ASC;

尝试将日期定义为FROM中单独子查询的字段，以避免对每个记录进行计算