Bug #13024 optimizer does not use index when where has (field1,field2,..)=(value1,value2,.
Submitted: 6 Sep 2005 23:04 Modified: 7 Sep 2005 16:55
Reporter: Jason Pyeron Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:4.1.14, 5.0.11 OS:Any
Assigned to: CPU Architecture:Any
Tags: performance, subquery

[6 Sep 2005 23:04] Jason Pyeron
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
[6 Sep 2005 23:10] Jason Pyeron
queries above have been running for hours, when it uses the index, it runs for seconds.

  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME CPU COMMAND
  815 mysql     25   0 22176  21M  1992 R    98.3  4.3 269:46   0 mysqld
[7 Sep 2005 7:16] Gleb Paharenko
In my opinion MySQL is behaving according to the manual:
  http://dev.mysql.com/doc/mysql/en/row-subqueries.html
[7 Sep 2005 11:51] Valeriy Kravchuk
According to the documentation (http://dev.mysql.com/doc/mysql/en/row-subqueries.html):

"For example, the following two statements are semantically equivalent (although currently only the second one can be optimized):

SELECT * FROM t1 WHERE (column1,column2) = (1,1);
SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;"

So, you should use second form as a workaround, and hope that this (natural) feature will be implemented later.
[7 Sep 2005 16:55] Jason Pyeron
This is not a feature request, unless there is a workaround.

Using only one serverside prepared SQL statement and using one only one constant there is no other way to write this query optimally.
[13 Jun 2006 4:24] Roberto Spadim
if right value is a constant this query can be easy optimized..
for example
(a,b)=(1,2)

a=1 and b=2
[29 Mar 2011 18:36] Roberto Spadim
please close, it is working nice now with 5.5