file_revisions table optimization
msameer - November 22, 2006 - 09:52
| Project: | Drupal |
| Version: | 4.7.4 |
| Component: | file system |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | duplicate |
Jump to:
Description
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 | |
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+----------------------------------------------+
#1
It's upload.module on line 829 (
function upload_load()).#2
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)
#3
The key should be a compound on (vid, fid) to get rid of the filesort.
#4
Duplicate of http://drupal.org/node/73045