[development] Drupal.org slow queries analyzed with explain: CVS tables

[Kieran Lal]

Hi, here are 3 queries that are showing up in the MySQL slow query log.

Worst case: you get a "using filesort" which means is writing* a  
special table to disk to execute the query.   MySQL on Drupal.org:  
Tmp Table Size : 536870912 The query type is all which means it's not  
using the index and reading every row from the table for some reason.
Cause: query on CVS_messages   is against all 29468 rows and then  
it's probably done for both repositories listed in cvs_repositories.  
What are the two entries in the cvs_repository table, is a table with  
2 rows?

mysql> EXPLAIN SELECT DISTINCT m.*, r.*, u.name, u.uid FROM  
cvs_messages m  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 20280, 10;
+-------+--------+---------------+---------+---------+-------+------- 
+---------------------------------+
| table | type   | possible_keys | key     | key_len | ref   | rows   
| Extra                           |
+-------+--------+---------------+---------+---------+-------+------- 
+---------------------------------+
| m     | ALL    | uid,rid       | NULL    |    NULL | NULL  | 29468  
| Using temporary; Using filesort |
| r     | ALL    | PRIMARY       | NULL    |    NULL | NULL  |     2  
| Using where                     |
| u     | eq_ref | PRIMARY       | PRIMARY |       4 | m.uid |     1  
|                                 |
+-------+--------+---------------+---------+---------+-------+------- 
+---------------------------------+
3 rows in set (0.00 sec)

Worst case: using filesort.  The first query is of type index which  
means the full index is being read, which is better than all but  
still very bad.
Cause: Group By causes the where.  Temp table can't be done in memory  
so it results in file sort.

mysql> EXPLAIN SELECT COUNT(DISTINCT(m.cid)) AS count, m.uid, u.name  
FROM cvs_messages m INNER JOIN users u ON m.uid = u.uid AND m.uid !=  
0 AND m.created > 1150344858 GROUP BY m.uid ORDER BY count DESC LIMIT  
0, 15;
+-------+--------+---------------+---------+---------+-------+------- 
+----------------------------------------------+
| table | type   | possible_keys | key     | key_len | ref   | rows   
| Extra                                        |
+-------+--------+---------------+---------+---------+-------+------- 
+----------------------------------------------+
| m     | index  | uid           | uid     |       4 | NULL  | 29468  
| Using where; Using temporary; Using filesort |
| u     | eq_ref | PRIMARY       | PRIMARY |       4 | m.uid |     1  
|                                              |
+-------+--------+---------------+---------+---------+-------+------- 
+----------------------------------------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT COUNT(DISTINCT m.cid) FROM cvs_messages m   
INNER JOIN users u ON m.uid = u.uid;
+-------+--------+---------------+---------+---------+-------+------- 
+-------------+
| table | type   | possible_keys | key     | key_len | ref   | rows   
| Extra       |
+-------+--------+---------------+---------+---------+-------+------- 
+-------------+
| m     | ALL    | uid           | NULL    |    NULL | NULL  | 29468  
|             |
| u     | eq_ref | PRIMARY       | PRIMARY |       4 | m.uid |     1  
| Using index |
+-------+--------+---------------+---------+---------+-------+------- 
+-------------+
2 rows in set (0.00 sec)

Any suggestions?

Kieran


TABLE SCHEMAS

desc 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        
|                |
+------------+---------------------+------+-----+--------- 
+----------------+
9 rows in set (0.00 sec)

desc cvs_messages;
+---------+------------------+------+-----+---------+-------+
| Field   | Type             | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| cid     | int(10) unsigned |      | PRI | 0       |       |
| rid     | int(10) unsigned |      | MUL | 0       |       |
| created | int(10) unsigned |      |     | 0       |       |
| user    | varchar(255)     |      |     |         |       |
| message | text             | YES  |     | NULL    |       |
| uid     | int(10) unsigned |      | MUL | 0       |       |
+---------+------------------+------+-----+---------+-------+
6 rows in set (0.10 sec)