Description:
http://projects.pdinc.us/show_bug.cgi?id=444
sent to mysql user list as question: http://lists.mysql.com/mysql/188785
there is an unique key index 'pathref_2 (pathref,version)' on this table.
so why does 'explain select * from files where (pathref,version)=(129286,0);' scan the whole table?
CREATE TABLE files (
id int(11) NOT NULL auto_increment,
pathref int(11) NOT NULL default '0',
version int(11) NOT NULL default '0',
mdate bigint(20) default NULL,
ddate datetime default NULL,
size bigint(20) default NULL,
md5 varchar(32) default NULL,
PRIMARY KEY (id),
UNIQUE KEY pathref_2 (pathref,version),
KEY pathref (pathref),
KEY version (version),
KEY mdate (mdate),
CONSTRAINT files_ibfk_1 FOREIGN KEY (pathref) REFERENCES paths (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql> select count(*) from files;
+----------+
| count(*) |
+----------+
| 117079 |
+----------+
1 row in set (0.07 sec)
mysql> explain select * from files where (version,pathref)=(select max(version),pathref from files where pathref=129286 group by pathref);
+----+-------------+-------+------+-------------------+-----------+---------+-------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-------------------+-----------+---------+-------+--------+--------------------------+
| 1 | PRIMARY | files | ALL | NULL | NULL | NULL | NULL | 117299 | Using where |
| 2 | SUBQUERY | files | ref | pathref_2,pathref | pathref_2 | 4 | const | 1 | Using where; Using index |
+----+-------------+-------+------+-------------------+-----------+---------+-------+--------+--------------------------+
2 rows in set (0.00 sec)
mysql> show index from files;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| files | 0 | PRIMARY | 1 | id | A | 117299 | NULL | NULL | | BTREE | |
| files | 0 | pathref_2 | 1 | pathref | A | 117299 | NULL | NULL | | BTREE | |
| files | 0 | pathref_2 | 2 | version | A | 117299 | NULL | NULL | | BTREE | |
| files | 1 | pathref | 1 | pathref | A | 117299 | NULL | NULL | | BTREE | |
| files | 1 | version | 1 | version | A | 117299 | NULL | NULL | | BTREE | |
| files | 1 | mdate | 1 | mdate | A | 117299 | NULL | NULL | YES | BTREE | |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
6 rows in set (0.21 sec)
mysql> explain select pathref,max(version) from files where pathref=129286 group by pathref;
+----+-------------+-------+------+-------------------+-----------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-------------------+-----------+---------+-------+------+--------------------------+
| 1 | SIMPLE | files | ref | pathref_2,pathref | pathref_2 | 4 | const | 1 | Using where; Using index |
+----+-------------+-------+------+-------------------+-----------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
mysql> select pathref,max(version) from files where pathref=129286 group by pathref;
+---------+--------------+
| pathref | max(version) |
+---------+--------------+
| 129286 | 0 |
+---------+--------------+
1 row in set (0.00 sec)
mysql> explain select * from files where (pathref,version)=(129286,0);
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | files | ALL | NULL | NULL | NULL | NULL | 117299 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.01 sec)
mysql> explain select * from files where pathref=129286 AND version=0;
+----+-------------+-------+-------+---------------------------+-----------+---------+-------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------------------+-----------+---------+-------------+------+-------+
| 1 | SIMPLE | files | const | pathref_2,pathref,version | pathref_2 | 8 | const,const | 1 | |
+----+-------------+-------+-------+---------------------------+-----------+---------+-------------+------+-------+
1 row in set (0.00 sec)
How to repeat:
see above
Suggested fix:
use index