[Resolved] AdRotate query issues (easily solved too!)
I do quite a bit of tech work, and on one site, I found that this plugin was causing serious performance issues and filling up the slow query log. Examples:
# Time: 120214 10:16:04 # User@Host: db_user[db_name] @ localhost  # Query_time: 5.142186 Lock_time: 0.000077 Rows_sent: 1 Rows_examined: 145277 SELECT SUM('clicks') as 'clicks', SUM('impressions') as 'impressions' FROM 'wp_adrotate_stats_tracker' WHERE 'ad' = '591';
145k rows to read for a single ad and I’m seeing this query fire multiple times a second. And the worst thing is that adding a very simple index solves the problem forever and drops the number of rows to 700 or so.
CREATE INDEX ad_idx ON wp_adrotate_stats_tracker (ad);
Here is another:
# Time: 111007 9:01:19 # User@Host: db_user[db_name] @ localhost  # Query_time: 8.488923 Lock_time: 0.000106 Rows_sent: 1 Rows_examined: 49715 SELECT COUNT(*) FROM 'wp_adrotate_tracker' WHERE 'ipaddress' = '188.8.131.52' AND 'stat' = 'i' AND 'timer' < '1317999371' AND '>bannerid' = '55' LIMIT 1;
By adding the following index, It is now examining 1 row instead of 50k:
CREATE INDEX ip_idx ON wp_adrotate_tracker (ipaddress);
I didn’t explore the other tables, but just a few simple indexes would make a staggering performance improvement. (And other plugin users – if the developers don’t add the indexes, you can still add them yourself to your own install.)
- The topic ‘[Resolved] AdRotate query issues (easily solved too!)’ is closed to new replies.