List:General Discussion« Previous MessageNext Message »
From:Jason Pyeron Date:September 6 2005 10:43pm
Subject:trouble understanding why query is not using the index.
View as plain text  
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.
Thread
trouble understanding why query is not using the index.Jason Pyeron7 Sep
  • Re: trouble understanding why query is not using the index.Dan Nelson7 Sep
    • Re: trouble understanding why query is not using the index.Jason Pyeron7 Sep
      • Re: trouble understanding why query is not using the index.Gleb Paharenko7 Sep
        • Re: trouble understanding why query is not using the index.Jason Pyeron7 Sep
          • Re: trouble understanding why query is not using the index.Gleb Paharenko8 Sep