はじめまして、雨柳と言います。

仕事の関係でMySQLを勉強し始めたところなのですが、
以下の条件で検索を行うと、索引があるに関わらず、全件をなめます。


create table TBL_A( COL_A char(10), PRIMARY KEY (COL_A));
create table TBL_B( COL_B char(10), PRIMARY KEY (COL_B));


mysql> desc TBL_A;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| COL_A | char(10) |      | PRI |         |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> desc TBL_B;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| COL_B | char(10) |      | PRI |         |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)


TBL_A、TBL_Bともに0000000000〜0000000009の１０件挿入する。


mysql> explain select COL_A from TBL_A where COL_A in (select COL_B from TBL_B where COL_B like '0000000001');
+----+--------------------+-------+-----------------+---------------+---------+---------+------+------+--------------------------+
| id | select_type        | table | type            | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+--------------------+-------+-----------------+---------------+---------+---------+------+------+--------------------------+
|  1 | PRIMARY            | TBL_A | index           | NULL          | PRIMARY |      20 | NULL |   10 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | TBL_B | unique_subquery | PRIMARY       | PRIMARY |      20 | func |    1 | Using index; Using where |
+----+--------------------+-------+-----------------+---------------+---------+---------+------+------+--------------------------+
2 rows in set (0.00 sec)


テーブル TBL_A に対し、全件なめているようです。
以下ですと、そうではないのですが。。。。


mysql> explain select COL_A from TBL_A where COL_A in ('0000000001');
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | TBL_A | const | PRIMARY       | PRIMARY |      20 | const |    1 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select COL_A from TBL_A where COL_A like (select COL_B from TBL_B where COL_B like '0000000001');
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | PRIMARY     | TBL_A | range | PRIMARY       | PRIMARY |      20 | NULL |    1 | Using where; Using index |
|  2 | SUBQUERY    | TBL_B | ALL   | PRIMARY       | NULL    |    NULL | NULL |    1 |                          |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
2 rows in set (0.00 sec)


この現象は、ＭｙＳＱＬの仕様でしょうか？
それとも、設定等の問題でしょうか？

ご教授ください。よろしくお願いします。




---------------------------------
GANBARE! NIPPON!
Yahoo! JAPAN JOC OFFICIAL INTERNET PORTAL SITE
http://mail.ganbare-nippon.yahoo.co.jp/