Before adding an index on fid:
desc SELECT i.title, i.timestamp, i.link FROM aggregator_item i WHERE i.fid =5 ORDER BY i.timestamp DESC LIMIT 0, 20;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
| 1 | SIMPLE | i | ALL | NULL | NULL | NULL | NULL | 2069 | Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
After adding an index on fid:
alter table aggregator_item add index(fid);
desc SELECT i.title, i.timestamp, i.link FROM aggregator_item i WHERE i.fid =5 ORDER BY i.timestamp DESC LIMIT 0, 20;
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | i | ref | fid | fid | 4 | const | 93 | Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+
Comment | File | Size | Author |
---|---|---|---|
#2 | aggregrator-better-key.patch | 926 bytes | bdragon |
Comments
Comment #1
FiReaNGeL CreditAttribution: FiReaNGeL commentedAdding an index to (fid,timestamp) get rid of the 'using filesort' clause. This should yield a performance improvement with big feeds - didn't have any to benchmark with.
Drupal 5.0 could benefit from this, as it have only an index on 'fid'.
Comment #2
bdragon CreditAttribution: bdragon commentedHere's a patch for DRUPAL-5.
As an aside, this problem has been partially fixed in HEAD and forgotton in DRUPAL-4-7.
Here's the original issue.
http://drupal.org/node/14400
Adding timestamp to the key reduces the need to filesort, I agree.
And this time, let's remember to backport the fix, heh.
Comment #3
FiReaNGeL CreditAttribution: FiReaNGeL commentedLooks great. All we need now is benchmarks on significantly large feeds, with concurrency if possible.
Comment #4
drummComment #5
Zen CreditAttribution: Zen commentedPatch applies, but:
- There is no update path.
- I believe that it should be investigated if an additional index (fid, timestamp) should be added or the existing one modified..
- code style issue with the commas in the index.
-K
Comment #6
msameer CreditAttribution: msameer commentedI can't do anything for the patch anymore. I'm not using core agggregator anymore. It also seems that the patch has a low priority.
Comment #7
catchDuplicate of: http://drupal.org/node/164532