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

[Gerhard Killesreiter]

Kieran Lal wrote:
> 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)

I am not sure we need the DISTINCT here. At least, we probably do not 
need if to act on all of the selects.


> 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)

Shouldn't m.cid be distinct on its own?



> 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)
> 


Same here.

> 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       |       |


Yeah, it is the primary key.

Cheers,
	Gerhard