How to optimize IN subquery in MySQL?

Question

My query seems to be very similar to example from http://dev.mysql.md/doc/refman/5.1/en/in-subquery-optimization.html, but unfortunately receipts from this manual don't work for me.

When I run them separately, both inner and outer queries use indexes, but when I run the whole query, outer query scans the whole table...

mysql> explain select smsId FROM SMSDelivery WHERE smsId IN (SELECT smsId FROM SMS WHERE phoneNumber='123456' OR fromUser='5678p' OR toUser='5124p') \G

  *************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
  table: SMSDelivery
  type: index
  possible_keys: NULL
  key: FK75C784D70BE5EC9
  key_len: 4
  ref: NULL
  rows: 1337017
  Extra: Using where; Using index

*************************** 2. row ***************************
     id: 2
    select_type: DEPENDENT SUBQUERY
    table: SMS
     type: unique_subquery
    possible_keys: PRIMARY,phoneNumber,fromUser,toUser
    key: PRIMARY
     key_len: 4
     ref: func
     rows: 1
     Extra: Using where
    2 rows in set (0.00 sec)

Jon Black · Accepted Answer · 2010-11-07 16:18:23Z

1

can you try this:

EXPLAIN
SELECT
 sd.* 
FROM 
 SMSDelivery sd
INNER JOIN
(
 SELECT smsId FROM SMS WHERE phoneNumber='123456' OR fromUser='5678p' OR toUser='5124p'
) s ON sd.smsId = s.smsId;

answered Nov 7, 2010 at 16:18

Jon Black

16.6k5 gold badges45 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vitaly Karasik DevOps Over a year ago

thank you very much - inner join uses indexes, not table scan!

Gavindra Kalikapersaud Over a year ago

You could also use an 'EXISTS' instead of an 'IN'

Collectives™ on Stack Overflow

How to optimize IN subquery in MySQL?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related