4

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 ?

3
  • 1
    Use a join instead of a subquery.
    – Matt Ball
    Jun 5 '13 at 3:00
  • so subquery wont use index?
    – AaronJiang
    Jun 5 '13 at 3:08
  • 1
    @AaronJiang IN a list, is like an OR of each item. Join is faster.
    – xagyg
    Jun 5 '13 at 3:45
4

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;
1
  • 2
    If you advertise join, use JOIN explicitly, not the old (implicit join) syntax with WHERE. Jun 5 '13 at 9:35
0

It said to be a const type in your picture. From MySQL document

The table has at most one matching row, which is read at the start of the query. Because there is only one row, values from the column in this row can be regarded as constants by the rest of the optimizer. const tables are very fast because they are read only once.

Since (select accountType from ec_provider where id = 3) will always return one constant value, MySQL will query this query first and reuse that value in the outer select. To trigger MySQL to use index, you can set (id, accountType) as primary key instead of only id as primary key which allows a single ID to have multiple accountType (if this really is what you want)

1
  • 1
    Understood, I may switch to join query other than changing indexes. :)
    – AaronJiang
    Jun 5 '13 at 3:55
-1

If your AccountType in the subquery is a varchar and the AccountTypeID is an Integer - this scenario might happen. MySQL would do a Full Table Scan

1
  • both accounttype and accounttypeid are TINYINT(1)
    – AaronJiang
    Jun 5 '13 at 3:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy