ag211I run a large scale website and the plugin gets totally bogged down when you reach about 100,000 records. In my one month of use, the plugin generated almost 400,000 records (13MB) in my database and now it has slowed down my entire site due to the fact it is checking so many rows.
Anyone know a good way to solve this? My server can't handle this kinda load anymore and I only need stats for the past 7 days and no more.
A good idea would be to add some functionality to the plugin that would allow you to specify how long you want your raw stats to be stored before they are purged from the database. Having a larger site, this is definitely an issue because the database eventually becomes too large especially if you only need stats for a particular time frame.
# Query_time: 12 Lock_time: 0 Rows_sent: 5 Rows_examined: 391745
SET timestamp=1250161790;
SELECT wp_posts.ID, wp_posts.post_title , (SUM(wp_popularpostsdata.pageviews)/(IF ( DATEDIFF(CURDATE(), MIN(wp_popularpostsdata.day)) > 0, DATEDIFF(CURDATE(), MIN(wp_popularpostsdata.day)), 1) )) AS 'avg_views' , (SELECT wp_users.display_name FROM wp_users WHERE wp_users.ID = wp_posts.post_author ) AS 'display_name', wp_posts.post_date_gmt AS 'date_gmt' FROM wp_posts LEFT JOIN wp_popularpostsdata ON wp_posts.ID = wp_popularpostsdata.postid WHERE post_status = 'publish' AND post_password = '' AND wp_popularpostsdata.day >= '2009-08-13' - INTERVAL 7 DAY AND pageviews > 0 AND wp_posts.post_type = 'post' GROUP BY postid ORDER BY avg_views DESC LIMIT 5;
