SQL:in clause with query taking too long compared to in clause with actual data

Question

I have 3 SQL queries as given:

1. select student_id from user where user_id =4; // returns 35
2. select * from student where student_id in (35);
3. select * from student where student_id in (select student_id from user where 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.

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!

Answer 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.

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.

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

EDIT:

In your case, you should use a JOIN method.

Answer 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
;