SQL：in子句，与带有实际数据的in子句相比，查询占用时间过长

I have 3 SQL queries as given:

我给出了3个SQL查询：

select student_id from user where user_id =4; // returns 35
从user_id = 4的用户中选择student_id; //返回35
select * from student where student_id in (35);
select * from student where student_id in（35）;
select * from student where student_id in (select student_id from user where user_id =4);
select * from student where student_id in（select user_id = user_id = 4）;

first 2 queries take less than 0.5 second, but the third, similar as 2nd containing 1st as subquery, is taking around 8 seconds.

前2个查询的时间少于0.5秒，但第三个查询类似于第2个查询包含第1个子查询，大约需要8秒。

I indexed tables according to my need, but time is not reducing.

我根据自己的需要为表编制索引，但时间并没有减少。

Can someone please give me a solution or provide some explanation for this behaviour.

有人可以给我一个解决方案或提供一些解释这种行为。

Thanks!

谢谢！

2 个解决方案

#1

Actually, MySQL execute the inner query at the end, it scans every indexes before. MySQL rewrites the subquery in order to make the inner query fully dependent of the outer one.

实际上，MySQL在最后执行内部查询，它之前会扫描每个索引。 MySQL重写子查询以使内部查询完全依赖于外部查询。

For exemple, it select * from student (depend of your database, but could return many results), then apply the inner query user_id=4 to the previous result.

例如，它从学生中选择*（取决于您的数据库，但可以返回许多结果），然后将内部查询user_id = 4应用于上一个结果。

The dev team are working on this problem and it should be "solved" in the 6.0 http://dev.mysql.com/doc/refman/5.5/en/optimizing-subqueries.html

开发团队正在解决这个问题，它应该在6.0 http://dev.mysql.com/doc/refman/5.5/en/optimizing-subqueries.html中“解决”。

EDIT:

编辑：

In your case, you should use a JOIN method.

在您的情况下，您应该使用JOIN方法。

#2

Not with a subquery but why don't you use a join here?

不是子查询，但为什么不在这里使用连接？

select
  s.*
from
  student s 
inner join 
  user u 
on s.id_student_id = u.student_id
where
  u.user_id = 4
;

#1