Why did mysql not use the primary key index in the main query after using the subquery

  mysql, question

图片描述

图片描述

This is a very, very old problem.

Mysql’s in subquery will always be converted into an exists query, see the manual for details.
http://dev.mysql.com/doc/refman/5.7/en/subquery-restrictions.html
Article 1 limitation.

Show me:

mysql> explain extended select id from yanse where name in (select name from yan
 se);
 ...
 
 mysql> show warnings;
 Plus-plus-plus-plus-
 --------------------------------------------------------------------------------
 -plus
 | Level | Code | Message
 
 |
 Plus-plus-plus-plus-
 --------------------------------------------------------------------------------
 -plus
 | Note  | 1003 | select `test1`.`yanse`.`id` AS `id` from `test1`.`yanse` where
 <in_optimizer>(`test1`.`yanse`.`name`,<exists>(select 1 from `test1`.`yanse` whe
 re (<cache>(`test1`.`yanse`.`name`) = `test1`.`yanse`.`name`))) |
 Plus-plus-plus-plus-
 --------------------------------------------------------------------------------
 -plus
 1 row in set (0.00 sec)

The difference between in and exists:
https://asktom.oracle.com/pls/asktom/f? p=100:11:::::P11_QUESTION_ID:953229842074

Select * from T1 where x in ( select y from T2 )

is typically processed as:

select * from t1, ( select distinct y from t2 ) t2 where t1.x = t2.y;

The subquery is evaluated, distinct’ed, indexed (or hashed or sorted)
and then joined to the original table — typically.

As opposed to

select * from t1 where exists ( select null from t2 where y = x )

That is processed more like:

>    for x in ( select * from t1 )    loop
 >       if ( exists ( select null from t2 where y = x.x )
 >       then
 >          OUTPUT THE RECORD
 >       end if    end loop