join和in和exists效率对比

 

优化sql，原sql：

select distinct q.* from aws_question q inner join aws_topic_question tq

on ( tq.topic_id=12564 or tq.topic_id=4558 or tq.topic_id=934 ) and q.question_id = tq.question_id

where q.category_id = 1 and q.status = 'CHECKED' and q.add_time <= '1496905791' and q.fenlei_id in (100,101)

order by q.add_time desc limit 0,5

 

条件: 除了两张表的主键索引，给aws_quesrion表 添加索引 add_time_fenlei_id（add_time和fenlei_id的结合）

(1) exists : 5 rows in set (0.01 sec)

select q.* from aws_question q

where exists (
select tq.question_id from aws_topic_question tq
where tq.topic_id in (12564,4558, 934) and tq.question_id=q.question_id
group by question_id
)

and q.category_id = 1 and q.status = 'CHECKED' and q.add_time <= '1496905791' and q.fenlei_id in (100,101)

order by q.add_time desc limit 0,5

=》执行结果：  5 rows in set (0.01 sec)

 

(2)in : 5 rows in set (46.91 sec)

select q.* from aws_question q

where q.question_id in (
select tq.question_id from aws_topic_question tq
where tq.topic_id in (12564,4558, 934)
group by question_id
)

and q.category_id = 1 and q.status = 'CHECKED' and q.add_time <= '1496905791' and q.fenlei_id in (100,101)

order by q.add_time desc limit 0,5

=》执行结果： 5 rows in set (46.91 sec)

 

三条sql的explain分析：

 

最后结论： exists效率最高，望高手指教！！！