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 |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+

CommentFileSizeAuthor
#2 aggregrator-better-key.patch926 bytesbdragon
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

FiReaNGeL’s picture

Title: Index for aggregator_item » Possible speedup: Add an index for aggregator_item

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'.

bdragon’s picture

Version: 4.7.4 » 5.x-dev
Assigned: Unassigned » bdragon
Status: Active » Needs review
FileSize
926 bytes

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.

FiReaNGeL’s picture

Looks great. All we need now is benchmarks on significantly large feeds, with concurrency if possible.

drumm’s picture

Version: 5.x-dev » 6.x-dev
Zen’s picture

Status: Needs review » Needs work

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

msameer’s picture

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.

catch’s picture

Status: Needs work » Closed (duplicate)