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?
I can rewrite the query, but for reasons out of my control I have to
design a query which takes single value for a pathref, as demonstrated in the first query.
Can anyone shed some light on this?
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)
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
- -
- Jason Pyeron PD Inc. http://www.pdinc.us -
- Partner & Sr. Manager 7 West 24th Street #100 -
- +1 (443) 921-0381 Baltimore, Maryland 21218 -
- -
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
This message is for the designated recipient only and may contain
privileged, proprietary, or otherwise private information. If you
have received it in error, purge the message from your system and
notify the sender immediately. Any other use of the email by you
is prohibited.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
