I am running ad statistics on a site that recieves a large number of ads and clickthrus and viewing the ad node loads very slowly. I tracked down this problem using mytop to the following sort of query:
SELECT SUM(s.count) FROM ad_statistics s LEFT JOIN ads a ON s.aid = a.aid LEFT JOIN term_node n ON a.aid = n.nid WHERE action = 'view' AND
date >= 2008120000 AND date <= 2009010000 AND n.tid = 98;
It seems that this query and some others like it are causing this page to take up to 30 seconds to load. I have turned on mysql slow query logging and most of the slow querys are coming from this module. Is there any plan to speed up these querys? Or any way to cache them? I turned on statistics caching in the settings for the module but it doesn't seem to have improved performance at all.
FYI here are the statistics for this ad:
Statistics
Views Clicks Click-thru
Today 27 0 0.00%
Yesterday 2614 8 0.31%
Last seven days 22820 121 0.53%
This month 25270 147 0.58%
Last month 122620 591 0.48%
This year 25270 147 0.58%
Last year 729573 3504 0.48%
All time 754843 3651 0.48%

Comments

jeremy’s picture

jeremy commented
Status: Active » Fixed

I recommend you enable the file cache that comes with the ad module to improve performance.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

jbfp’s picture

jbfp commented

Do you know if it's safe to truncate the ad_statistics table in phpMyAdmin? Or is there a way to clear out the ad_statistics table through a Drupal interface or to set it so that it doesn't store statistics older than a specified period of time? I'm using Drupal 6.11.

WeaveGeek’s picture

WeaveGeek commented

I'm also curious about this. My ad_statistics table is at 21 MB and still growing. Is there some maintenance I should be doing to compress and truncate it?

joshuautley’s picture

joshuautley commented

Subscribe