5

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!

thekosmix
  • 1,705
  • 21
  • 35

2 Answers2

5

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.

Edgar
  • 158
  • 1
  • 6
  • thanks for your explanation but if I'm executing the subquery in query 3, separately it takes less than 0.5 seconds, and the complex query takes 8 seconds. Even if query optimizer rewrites the subquery, why is it taking around 20 times compared to query 2? – thekosmix Jun 27 '13 at 06:36
  • @thekosmix : Because the query `SELECT *` parses every entries and the subquery is executed for every results. For exemple, if you have 4 students, the query will select ALL 4 students, then for each one it will check the subquery (`user_id =4`). Don't forget the subquery is execute at the end. Separately the subquery is fast, but it is executing for EACH results of the inner query. The bigger your students table is, the longer the query will be. Most of the time, the operator `IN` is not good idea when you work on 2 tables. – Edgar Jun 27 '13 at 07:34
  • Thanks a lot, will try to avoid in query from now!! – thekosmix Jun 27 '13 at 08:15
1

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
;
martinw
  • 364
  • 2
  • 7