With 170 feeds in the aggregator and trimming the archived items to only the past 7 days, I find the following query used in building the admin/aggregator page is timing out on the browser, and taking a minute or more through the mysql command line:

SELECT f.*, COUNT(i.iid) AS items FROM aggregator_feed f LEFT JOIN aggregator_item i ON f.fid = i.fid GROUP BY f.fid;

Curiously, tracing this down I discovered the aggregator_item table still contained items from two years go (no expiry) and double curiously, I found that trimming the list of items down from two years worth (about 50,000 entries) to just a week's worth (about 5000) only improved the computation time for that query by about 20%.

I'm not sure if there is a quick fix for this (other than to remove the count from the display) -- a long-term fix might be to compute the feed item counts as items are added to the feed and then store this total in aggregator_feed instead of computing it on the fly; that value may be useful in other display pages as well.

CommentFileSizeAuthor
#6 fid_idx.patch1.31 KBchx
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

moshe weitzman’s picture

moshe weitzman CreditAttribution: moshe weitzman commented

the query looks fine to me. Do we have the proper indexes in place?

killes@www.drop.org’s picture

killes@www.drop.org CreditAttribution: killes@www.drop.org commented

CREATE TABLE aggregator_item (
iid int(10) NOT NULL auto_increment,
fid int(10) NOT NULL default '0',
title varchar(255) NOT NULL default '',
link varchar(255) NOT NULL default '',
author varchar(255) NOT NULL default '',
description longtext NOT NULL,
timestamp int(11) default NULL,
PRIMARY KEY (iid)
) TYPE=MyISAM;

No index on fid. guess somebody who uses this module should add one.

dopry’s picture

dopry CreditAttribution: dopry commented
Assigned: Unassigned » dopry

fid is still a candidate for getting indexed. I'll roll a patch after 4.7 rc hits.

Dries’s picture

Dries CreditAttribution: Dries commented

Feel free to roll a patch sooner than that. Looks like a critical performance improvement.

On drupal.org that query takes 4.8 seconds! :/

mysql> EXPLAIN SELECT f.*, COUNT(i.iid) AS items FROM aggregator_feed f LEFT JOIN aggregator_item i ON f.fid = i.fid GROUP BY f.fid;
+-------+------+---------------+------+---------+------+------+---------------------------------+
| table | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+-------+------+---------------+------+---------+------+------+---------------------------------+
| f     | ALL  | NULL          | NULL |    NULL | NULL |   63 | Using temporary; Using filesort |
| i     | ALL  | NULL          | NULL |    NULL | NULL | 6269 |                                 |
+-------+------+---------------+------+---------+------+------+---------------------------------+

mysql> ALTER TABLE aggregator_item ADD INDEX fid (fid);
Query OK, 6269 rows affected (0.45 sec)
Records: 6269  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT f.*, COUNT(i.iid) AS items FROM aggregator_feed f LEFT JOIN aggregator_item i ON f.fid = i.fid GROUP BY f.fid;
+-------+------+---------------+------+---------+-------+------+---------------------------------+
| table | type | possible_keys | key  | key_len | ref   | rows | Extra                           |
+-------+------+---------------+------+---------+-------+------+---------------------------------+
| f     | ALL  | NULL          | NULL |    NULL | NULL  |   63 | Using temporary; Using filesort |
| i     | ref  | fid           | fid  |       4 | f.fid |  103 |                                 |
+-------+------+---------------+------+---------+-------+------+---------------------------------+

Note the change in the number of rows. After adding an index, the query takes 0.61 seconds (still slow-ish).

chx’s picture

chx CreditAttribution: chx commented
Status: Active » Reviewed & tested by the community

Given the luminaries that followed up here, I am surprised this is not yet tackled.

I will roll one for 4.7 if this is in. We have branch support, yay!

chx’s picture

chx CreditAttribution: chx commented
FileSize
fid_idx.patch1.31 KB

Dude, where's the patch??

Dries’s picture

Dries CreditAttribution: Dries commented

Shouldn't that upgrade go in aggregator.install?

drumm’s picture

drumm
he/him
Location NY, US
CreditAttribution: drumm commented
Status: Reviewed & tested by the community » Fixed

Committed to HEAD.

drumm’s picture

drumm
he/him
Location NY, US
CreditAttribution: drumm commented

Dries- looks like we simultaneously reviewed.

I thought about this, but nothing else in core has broken out of system_update_N() yet. It would make a bunch of select menus on the upgrade selection page, but maybe thats a problem with that UI.

Anonymous’s picture

(not verified) CreditAttribution: commented
Status: Fixed » Closed (fixed)