这条SQL语句如何优化呢？

Question

select 
a.id , 
a.username, 
a.avatar,
(select COUNT(b.user_id) from poll_topics   b where (a.id=b.user_id) and (UNIX_TIMESTAMP(b.created_at) > UNIX_TIMESTAMP() - 86400) ) as topics,
(select COUNT(c.user_id) from user_votes    c where (a.id=c.user_id) and (UNIX_TIMESTAMP(c.created_at) > UNIX_TIMESTAMP() - 86400) ) as votes,
(select COUNT(d.user_id) from poll_comments d where (a.id=d.user_id) and (UNIX_TIMESTAMP(d.created_at) > UNIX_TIMESTAMP() - 86400) ) as comments,
(select COUNT(b.user_id) from poll_topics   b where (a.id=b.user_id) and (UNIX_TIMESTAMP(b.created_at) > UNIX_TIMESTAMP() - 86400) )*3+
(select COUNT(c.user_id) from user_votes    c where (a.id=c.user_id) and (UNIX_TIMESTAMP(c.created_at) > UNIX_TIMESTAMP() - 86400) )*2+
(select COUNT(d.user_id) from poll_comments d where (a.id=d.user_id) and (UNIX_TIMESTAMP(d.created_at) > UNIX_TIMESTAMP() - 86400) )*2 as active
from users a 
group by a.id 
order by active desc
limit 6;active字段是topics*3 + votes*2 + comments*2计算出来的，前面已经进行过一次查询，那么在计算active字段时是否还需要进行查询呢？有没有更好的写法呢？

Answer 1

 (UNIX_TIMESTAMP(b.created_at) > UNIX_TIMESTAMP() - 86400)这个判断导致无法使用索引。改为b.created_at>now()-interval 1 day然后在所有表上创建user_id，created_at)复合索引。

Answer 2

第二次查询，会执行的，但MYSQL对相同的SQL查询语句会直接从缓存中拿，速度可以几乎忽略不计。
你的语句改成如下
select  
a.id ,  
a.username,  
a.avatar,
(select COUNT(b.user_id) from poll_topics   where (a.id=user_id) and (created_at > now()-interval 1 day ) as topics,
(select COUNT(c.user_id) from user_votes    where (a.id=user_id) and (created_at > now()-interval 1 day ) as votes,
(select COUNT(d.user_id) from poll_comments where (a.id=user_id) and (created_at > now()-interval 1 day ) as comments,
(select COUNT(b.user_id) from poll_topics   where (a.id=user_id) and (created_at > now()-interval 1 day )*3+
(select COUNT(c.user_id) from user_votes    where (a.id=user_id) and (created_at > now()-interval 1 day )*2+
(select COUNT(d.user_id) from poll_comments where (a.id=user_id) and (created_at > now()-interval 1 day )*2 as active
from users a  
group by a.id  
order by active desc
limit 6;或者改成JOIN方式。

Answer 3

1、SQL语句不是标准的SQL语句；
2、子查询修改为INNER JOIN