Bug #13024 optimizer does not use index when where has (field1,field2,..)=(value1,value2,.
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
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.055 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.