Impossible WHERE noticed after reading const tables

動機

rails-footnotes をちょろりといじってインデックスを使わないクエリを使ったら警告っぽいものがでるようにしてみた。開発中のアプリでテストがてらに使ってもらったらインデックスを張っているのに「key」の値が設定されないクエリがあるよと報告が。extraに「Impossible WHERE noticed after reading const tables」ってメッセージがでている。なんじゃこれと思って調べた。

先に結果

あるクエリにてwhere条件の検索にユニークインデックスが使われることになった場合にデータがヒットしなかった場合に発生すると思われる（主キーも含む）。ノーマルのインデックスが使われた結果データがヒットしなかった場合は発生しない。

確認方法

テーブル作成
CREATE TABLE `books` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(10) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `idx01` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

データ作成
select * from books;
+----+------------+
| id | name       |
+----+------------+
|  3 | death note | 
|  2 | dragon b   | 
|  1 | one piece  | 
+----+------------+
3 rows in set (0.00 sec)

explain select * from books where name = 'one piece';
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | books | ref  | idx01         | idx01 | 12      | const |    1 | Using where; Using index | 
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+

explain select * from books where name = 'two piece';
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | books | ref  | idx01         | idx01 | 12      | const |    1 | Using where; Using index | 
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+

ユニークインデックスに変更
show create table books;
CREATE TABLE `books` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(10) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idx01` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

explain select * from books where name = 'one piece';
+----+-------------+-------+-------+---------------+-------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+-------+---------+-------+------+-------------+
|  1 | SIMPLE      | books | const | idx01         | idx01 | 12      | const |    1 | Using index | 
+----+-------------+-------+-------+---------------+-------+---------+-------+------+-------------+

explain select * from books where name = 'two piece';
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables | 
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+

explain select * from books where id = 5;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables | 
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+

文字コードがlatin1なのは愛嬌と言うことで