Drupal 6.12

I am running a high load server with Drupal. The aggregator table is only 372 rows, but several databases are managed on the server, with a few Gb. i log slow queries which execute in more than 30 ms.

Surprisingly, this query was logged as "slow":
2009-05-15 10:20:14 CEST LOG: durée : 116.562 ms, instruction : SELECT iid FROM aggregator_item WHERE fid = 47 AND title = ''

Now running explain analyse:

EXPLAIN ANALYSE
SELECT iid FROM aggregator_item WHERE fid = 47 AND title = ''

"Index Scan using aggregator_item_fid_idx on aggregator_item (cost=0.00..8.27 rows=1 width=4) (actual time=0.015..0.015 rows=0 loops=1)"
" Index Cond: (fid = 47)"
" Filter: ((title)::text = ''::text)"
"Total runtime: 0.039 ms"

After adding an index, the query disappeared of slow logs. It was executed in less than 30 ms.

Please find attached a patch. This patch needs some work to upgrade existing tables.

CommentFileSizeAuthor
aggregator.diff361 bytesgrub3
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

alex_b’s picture

alex_b CreditAttribution: alex_b commented

I'd actually argue that aggregator should not deduplicate by title.

But deduplication is such a contentious issue that if people want to keep title deduping (and the good argument in favor here is that this is what's running on possibly 1000s of drupal sites out there), I think this approach is the way to go.

verta’s picture

verta CreditAttribution: verta commented
Title: Agreegator needs index on title » Aggregator needs index on title

Status: Needs work » Closed (outdated)

Automatically closed because Drupal 6 is no longer supported. If the issue verifiably applies to later versions, please reopen with details and update the version.