[drupal-devel] MySQL indices not being picked up

[Dries Buytaert]

Does anyone know why MySQL does not use indices/keys for the following  
query despite the fact it has plenty of indices to choose from ...

mysql> EXPLAIN SELECT DISTINCT m.*, r.*, u.name, u.uid FROM  
cvs_messages m INNER JOIN cvs_files f ON m.cid = f.cid INNER JOIN  
cvs_repositories r ON m.rid = r.rid INNER JOIN users u ON m.uid = u.uid  
ORDER BY m.created DESC LIMIT 0, 10;
+-------+--------+-------------------------------------+--------- 
+---------+-------+-------+---------------------------------+
| table | type   | possible_keys                       | key     |  
key_len | ref   | rows  | Extra                           |
+-------+--------+-------------------------------------+--------- 
+---------+-------+-------+---------------------------------+
| m     | ALL    | PRIMARY,rid_created,cid_created,uid | NULL    |     
NULL | NULL  | 15732 | Using temporary; Using filesort |
| f     | ref    | cid,cid_2                           | cid     |       
  4 | m.cid |     3 | Using index                     |
| r     | ALL    | PRIMARY                             | NULL    |     
NULL | NULL  |     2 | Using where                     |
| u     | eq_ref | PRIMARY                             | PRIMARY |       
  4 | m.uid |     1 |                                 |
+-------+--------+-------------------------------------+--------- 
+---------+-------+-------+---------------------------------+

mysql> EXPLAIN cvs_messages;
+---------+------------------+------+-----+---------+-------+
| Field   | Type             | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| cid     | int(10) unsigned |      | PRI | 0       |       |
| rid     | int(10) unsigned |      | MUL | 0       |       |
| created | int(10) unsigned |      | MUL | 0       |       |
| user    | varchar(255)     |      | MUL |         |       |
| message | text             | YES  |     | NULL    |       |
| uid     | int(10) unsigned |      | MUL | 0       |       |
+---------+------------------+------+-----+---------+-------+

mysql> EXPLAIN cvs_files;
+---------------+------------------+------+-----+---------+-------+
| Field         | Type             | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------+-------+
| cid           | int(10) unsigned |      | MUL | 0       |       |
| rid           | int(10) unsigned |      | MUL | 0       |       |
| file          | varchar(255)     |      | MUL |         |       |
| branch        | varchar(255)     |      | MUL |         |       |
| revision      | varchar(255)     |      |     |         |       |
| lines_added   | int(10) unsigned |      |     | 0       |       |
| lines_removed | int(10) unsigned |      |     | 0       |       |
| nid           | int(10) unsigned |      | MUL | 0       |       |
| uid           | int(10) unsigned |      | MUL | 0       |       |
+---------------+------------------+------+-----+---------+-------+

mysql> EXPLAIN cvs_repositories;
+------------+---------------------+------+-----+--------- 
+----------------+
| Field      | Type                | Null | Key | Default | Extra        
    |
+------------+---------------------+------+-----+--------- 
+----------------+
| rid        | int(10) unsigned    |      | PRI | NULL    |  
auto_increment |
| name       | varchar(255)        |      | UNI |         |              
    |
| root       | varchar(255)        |      |     |         |              
    |
| modules    | varchar(255)        |      |     |         |              
    |
| diffurl    | varchar(255)        |      |     |         |              
    |
| newurl     | varchar(255)        |      |     |         |              
    |
| trackerurl | varchar(255)        |      |     |         |              
    |
| method     | tinyint(2) unsigned |      |     | 0       |              
    |
| updated    | int(11) unsigned    |      |     | 0       |              
    |
+------------+---------------------+------+-----+--------- 
+----------------+

--
Dries Buytaert  ::  http://www.buytaert.net/