First of all, thanks for the nice plugin. Ive it running on a Tech-Network on several News-Blogs for about 1,5 Months. But...this problem has started to make me much problems.
In the last 4 Weeks i encountered several Crashes of the Webserver. My Provider supported me with a Slow-Query Log and some of em are related to the WPP Plugin. Ive wrote u some down, maybe u can work something out:
SELECT wpdb_posts.ID, wpdb_posts.post_title , (SUM(wpdb_popularpostsdatacache.pageviews)) AS 'pageviews' FROM wpdb_posts RIGHT JOIN wpdb_popularpostsdatacache ON wpdb_posts.ID = wpdb_popularpostsdatacache.id WHERE wpdb_posts.post_status = 'publish' AND wpdb_posts.post_password = '' AND gadget_popularpostsdatacache.day >= '2012-03-22 15:14:12' - INTERVAL 1 DAY AND wpdb_posts.post_type = 'post' GROUP BY wpdb_posts.ID ORDER BY pageviews DESC LIMIT 10;
# Query_time: 26.749901 Lock_time: 0.584690 Rows_sent: 10 Rows_examined: 7400
use db xxxxx;
SELECT xxx_posts.ID, xxx_posts.post_title , (SUM(xxx_popularpostsdatacache.pageviews)) AS 'pageviews' FROM xxx_posts RIGHT JOIN xxx_popularpostsdatacache ON xxx_posts.ID = xxx_popularpostsdatacache.id WHERE xxx_posts.post_status = 'publish' AND xxx_posts.post_password = '' AND xxx_popularpostsdatacache.day >= '2012-03-22 13:50:39' - INTERVAL 1 DAY AND xxx_posts.post_type = 'post' GROUP BY xxx_posts.ID ORDER BY pageviews DESC LIMIT 10;
Maybe you also have some quick tips how i can reduce the queries for the plugin.
PS: I use this code to show the most popular topics per day. Not using the Widget.
<?php if (function_exists('wpp_get_mostpopular')) wpp_get_mostpopular("range=daily&order_by=views&limit=10"); ?>
PSS: I use a Virtual Server Managed, most configured Version with about 4GB RAM. My Provider told me, it rans out of memory cuz of that many slow queries. Not sure about that. Also the Network has about 6k to 7k users a day with about 20k Page Impressions.