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:
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 query takes 1.2s Explain on this query results:
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
The second query:
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'
);
Takes 45sec to run, with explain:
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 query only by index fields?
Why both queries scans the full length of the user table?
Any ideas how to improve?
Thanks.
