I've found a query like this:

SELECT * FROM files f INNER JOIN file_revisions r ON f.fid = r.fid WHERE r.vid = 192 ORDER BY f.fid DESC;

I'm not sure which module is executing this query.

desc SELECT * FROM files f INNER JOIN file_revisions r ON f.fid = r.fid WHERE r.vid = 192 ORDER BY f.fid DESC;

+----+-------------+-------+--------+---------------+---------+---------+--------------------+------+----------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                | rows | Extra          |
+----+-------------+-------+--------+---------------+---------+---------+--------------------+------+----------------+
|  1 | SIMPLE      | f     | ALL    | PRIMARY       | NULL    |    NULL | NULL               | 2079 | Using filesort |
|  1 | SIMPLE      | r     | eq_ref | PRIMARY       | PRIMARY |       8 | foolab.f.fid,const |    1 |                |
+----+-------------+-------+--------+---------------+---------+---------+--------------------+------+----------------+

I've added an index on file_revisions. And now MySQL can use the key.

desc SELECT * FROM files f INNER JOIN file_revisions r ON f.fid = r.fid WHERE r.vid = 192 ORDER BY f.fid DESC;

+----+-------------+-------+--------+---------------+---------+---------+--------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref          | rows | Extra                                        |
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+----------------------------------------------+
|  1 | SIMPLE      | r     | ref    | PRIMARY,vid   | vid     |       4 | const        |    5 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | f     | eq_ref | PRIMARY       | PRIMARY |       4 | foolab.r.fid |    1 |                                              |
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+----------------------------------------------+

Comments

kkaefer’s picture

kkaefer commented

It's upload.module on line 829 (function upload_load()).

kkaefer’s picture

kkaefer commented

For Drupal 5, it returns (unpatched):

+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+---------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref               | rows | Extra                           |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+---------------------------------+
|  1 | SIMPLE      | r     | ref    | PRIMARY,vid   | vid     | 4       | const             |    1 | Using temporary; Using filesort | 
|  1 | SIMPLE      | f     | eq_ref | PRIMARY       | PRIMARY | 4       | drupal_head.r.fid |    1 |                                 | 
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+---------------------------------+
2 rows in set (0.00 sec)
FiReaNGeL’s picture

FiReaNGeL commented

The key should be a compound on (vid, fid) to get rid of the filesort.

bdragon’s picture

bdragon commented
Status: Active » Closed (duplicate)

Duplicate of http://drupal.org/node/73045