Select SUM(fen) sum from jifen where type='qiandao' and DATE_FORMAT( nl_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) and nl_uid in
(select uid from user where user_type='2')
## 数据太大查询速度太慢,3分钟都出不来,建立的索引无用!该怎么优化下
...全文
1856打赏收藏
帮我优化下这条sql
Select SUM(fen) sum from jifen where type='qiandao' and DATE_FORMAT( nl_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) and nl_uid in (select uid from user where user_type='2') ## 数据太大查询速度太慢,3分钟都出不来,建立的索引无用!该怎么优化下
Select SUM(fen) sum
from jifen inner join user ur on nl_uid = ur.uid and ur.user_type = '2'
where type = 'qiandao'
and nl_time between DATE_ADD(curdate(),interval -day(curdate())+1 day) and date_add(curdate()-day(curdate())+1,interval 1 month);
-- and DATE_FORMAT(nl_time, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')
-- and nl_uid in (select uid from user where user_type = '2')