mysql group 查询的替代_SQL 优化之用两表联查取代子查询

有两张表group_post帖子表，group_post_fav_idx_oid_0用户点赞帖子记录表，帖子id对10取模等于0的分表。

需求：取出点赞数>20,评论数>10的帖子，但是在group_post里面没有记录点赞数的字段，所以需要对点赞记录表做统计。

#50 rows in set (1 min 9.67 sec)

select id, name from group_post

where id>=144

and id<=21081

and id in ( select oid from group_post_fav_idx_oid_0 group by oid having count(*)>20 )

and id not in (90,210,263,950,964,1157)

and gid not in (0,90,165,166,179)

and cnum>10

order by last_ts limit 50

优化之后

#50 rows in set (0.09 sec)

select a.id, a.name from group_post a, group_post_fav_idx_oid_0 b

where a.id>=144

and a.id<21081

and a.cnum>100

and a.id=b.oid

and a.id not in (90,210,263,950,964,1157)

and a.gid not in (0,90,165,166,179)

group by b.oid

having count(b.oid)>20

order by a.last_ts limit 50