Possible speedup: Add an index for aggregator_item
| Project: | Drupal |
| Version: | 6.x-dev |
| Component: | aggregator.module |
| Category: | bug report |
| Priority: | normal |
| Assigned: | bdragon |
| Status: | duplicate |
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 |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+
#1
Adding 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'.
#2
Here'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.
#3
Looks great. All we need now is benchmarks on significantly large feeds, with concurrency if possible.
#4
#5
Patch 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
#6
I 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.
#7
Duplicate of: http://drupal.org/node/164532