title: mysql subquery strangely slow
body: <p>I have a query to select from another sub-query select. While the two queries look almost the same the second query (in this sample) runs much slower:</p>
  
  <pre><code>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'
                  );
  </code></pre>
  
  <p>This query takes 1.2s Explain on this query results:</p>
  
  <pre><code> 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
  </code></pre>
  
  <p>The second query:</p>
  
  <pre><code>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'
                  );
  </code></pre>
  
  <p>Takes 45sec to run, with explain:</p>
  
  <pre><code> 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
  </code></pre>
  
  <p>Why is it slower if i query only by index fields?
  Why both queries scans the full length of the user table?
  Any ideas how to improve?</p>
  
  <p>Thanks.</p>