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