MYSQL wont use index in “IN(SELECT ..)” sub clause

MySQL

I have two tables in MYSQL with target fields set as index / primary index already. Then I ran the below sql query.

  EXPLAIN 
  SELECT charge FROM ec_provider_account_option 
  WHERE accountPermissionId='4' 
  AND accountTypeId 
      IN (select accountType from ec_provider where id = 3)

enter image description here
According to the result no index ref used obviously. The in clause "select accountType from ec_provider where id = 3" uses index by querying alone.

Plus, if I replace the subsquery in(select .. from ..) with in(1,2,3), it will return the same result, and using index ref.

 EXPLAIN 
    SELECT charge FROM ec_provider_account_option 
    WHERE accountPermissionId='4' and accountTypeId 
          IN(1,2,3)

How comes that ?

Best Answer

That's right, so just use a join - MUCH faster:

SELECT charge FROM ec_provider_account_option a, 
                   ec_provider b
  WHERE a.accountTypeId = b.accountType
  AND a.accountPermissionId = '4' 
  AND b.id = 3;
Related Question