MySQLはサブクエリに弱い？

サブクエリが大好きなので、結構困ります。

バージョン

MySQL 5.0.45

準備

キーと値だけ持つ単純なInnoDBのテーブルに、100万行を用意。

mysql> desc for_index_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI |         |       | 
| name  | varchar(32) | YES  |     | NULL    |       | 
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.62 sec)

mysql> select count(*) from for_index_test;
+----------+
| count(*) |
+----------+
|  1000000 | 
+----------+
1 row in set (0.50 sec)

mysql> select * from for_index_test limit 5;
+----+-----------------------------+
| id | name                        |
+----+-----------------------------+
|  1 | rieetgsokhntbgqqrdubak      | 
|  2 | emdhtcwyewfelgoakl          | 
|  3 | lcsmsttvzyfzcyayzjhmsjilzrf | 
|  4 | ntqfleojudavxzyghsghkywc    | 
|  5 | itvubsbluvbuwuccdzocektxmhj | 
+----+-----------------------------+
5 rows in set (0.00 sec)

パフォーマンス測定

mysql> select name from for_index_test where id = 555555;
+---------------------------------+
| name                            |
+---------------------------------+
| vgduisldmirnkgisltkmqbobyjrdeye | 
+---------------------------------+
1 row in set (0.00 sec)

mysql> select name from for_index_test where id = (select id from for_index_test where id = 555555);
+---------------------------------+
| name                            |
+---------------------------------+
| vgduisldmirnkgisltkmqbobyjrdeye | 
+---------------------------------+
1 row in set (0.00 sec)

mysql> select name from for_index_test where id in (select id from for_index_test where id = 555555);
+---------------------------------+
| name                            |
+---------------------------------+
| vgduisldmirnkgisltkmqbobyjrdeye | 
+---------------------------------+
1 row in set (0.87 sec)

結果は、サブクエリの戻り値に in 判定かけたときだけやたら遅いです。

EXPLAINさせる

オプティマイザに説明を求めました。

mysql> explain  select name from for_index_test where id in (select id from for_
index_test where id = 555555)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: for_index_test
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 998421
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: for_index_test
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: Using index
2 rows in set (0.00 sec)

テーブルにフルスキャンかけてます。駄目ダこりゃ。

結論として、MySQLではサブクエリ+inの組み合わせは避けるべきってことですかね。

補足

この現象は、実践ハイパフォーマンスMySQLのP90で詳解されています。書籍には、もう直っている、と書かれてますが、まだ駄目みたいですね。