Mysql subquery strangely slow

Question

I have a query to select from another subquery selection. Although the two queries look almost the same, the second query (in this example) is much slower:

SELECT
   user.id
  ,user.first_name
  -- user.*
  FROM user
  WHERE
    user.id IN (SELECT ref_id 
                  FROM education 
                 WHERE ref_type='user' 
                   AND education.institute_id='58' 
                   AND education.institute_type='1'
                );

This request takes 1.2 seconds. Explain the results of this query:

 id select_type table   type    possible_keys   key key_len ref rows    Extra
 1  PRIMARY         user   index    first_name  152 141192  Using where; Using index
 2  DEPENDENT SUBQUERY  education   index_subquery  ref_type,ref_id,institute_id,institute_type,ref_type_2  ref_id  4   func    1   Using where

Second request:

SELECT
  -- user.id
  -- user.first_name
  user.*
  FROM user
  WHERE
    user.id IN (SELECT ref_id 
                  FROM education 
                 WHERE ref_type='user' 
                   AND education.institute_id='58' 
                   AND education.institute_type='1'
                );

It takes 45 seconds to start, with an explanation:

 id select_type table   type    possible_keys   key key_len ref rows    Extra
 1  PRIMARY user    ALL                 141192  Using where
 2  DEPENDENT SUBQUERY  education   index_subquery  ref_type,ref_id,institute_id,institute_type,ref_type_2  ref_id  4   func    1   Using where

Why is it slower if I request only index fields? Why do both queries scan the entire length of the user table? Any ideas for improvement?

Thank.

Answer 1

That's what I think:

The query planner will turn the query into an internal join, which gives the database the ability to start from any table while filtering the result.

, , , , , .

, , , . , , , .

(, - SQL Server, , .)

Answer 2

, , , . JOIN :

SELECT
    user.id
    user.first_name
FROM user
JOIN education 
ON user.id = education.ref_id 
AND education.ref_type='user' 
AND education.institute_id='58' 
AND education.institute_type='1'

Answer 3

I have had several instances of replacing “WHERE foo in (subquery)” with dumping the results of a subquery into a temporary table, and using an internal join has seriously improved performance. (For example, a 6.5-minute request turns into a subsecond request.)

That, uh, that's what Mark Byers just said.