查询语句优化

原查询：select tid, fid, subject, author, authorid from dz_forum_thread where authorid in (select uid from dz_common_member where groupid in (1, 3, 23)) and dateline > 1395663240 and highlight <> 40 （用时10s）


优化后：select tid, fid, subject, author, authorid from dz_common_member as m, dz_forum_thread as t where m.uid=t.authorid and m.groupid in (1,3,23) and t.dateline > 1395663240 and t.highlight <> 40

在同时取1000条记录的情况下

SELECT tid, fid, subject, author, authorid
FROM dz_common_member AS m, dz_forum_thread AS t
WHERE m.uid = t.authorid
AND m.groupid
IN ( 1, 3, 23 )
AND t.dateline > 1305663240
AND t.highlight <>40
LIMIT 1000

用时0.0148s



SELECT tid, fid, subject, author, authorid
FROM dz_common_member AS m, dz_forum_thread AS t
WHERE m.uid = t.authorid
AND (m.groupid=1 or m.groupid=3 or m.groupid=23)
AND t.dateline >1305663240
AND t.highlight <>40
LIMIT 1000

用时0.0122s

总结：
1. 尽量不要使用子查询

2. 如果in的数目固定且比较少，可以用or替换

查询语句优化,布布扣,bubuko.com

查询语句优化

原文：http://www.cnblogs.com/zhaozhilu/p/3620428.html