这条SQL语句如何优化呢？

[大伟]

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字段时是否还需要进行查询呢？有没有更好的写法呢？

[ACMAIN_CHM]

(UNIX_TIMESTAMP(b.created_at) > UNIX_TIMESTAMP() - 86400)

这个判断导致无法使用索引。改为

b.created_at>now()-interval 1 day

然后在所有表上创建user_id，created_at)复合索引。

[ACMAIN_CHM]

[Quote]前面已经进行过一次查询，那么在计算active字段时是否还需要进行查询呢？[/Quote]第二次查询，会执行的，但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方式。

[WWWWA]

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