MySQL query performance issues
We are experiencing server load/performance issues for our site that are very similar to the issues described in this post. A complicated MySQL query (see below) seems to be overtaxing our server as we have a busy site with very many posts. We’re using the latest version of WP (3.5.1) and WPPP (2.3.5). I’m going to disable comment counts to see if it helps. Can anything be done to improve performance and reduce the load on our server?
SELECT p.ID AS 'id', p.post_title AS 'title', p.post_date AS 'date', p.post_author AS 'uid' , IFNULL(v.pageviews, 0) AS 'pageviews' , IFNULL(c.comment_count, 0) AS 'comment_count' FROM wp_16_posts p LEFT JOIN (SELECT id, SUM(pageviews) AS 'pageviews', day FROM (SELECT id, pageviews, day FROM wp_16_popularpostsdatacache WHERE day > DATE_SUB('2013-05-21 04:22:39', INTERVAL 1 MONTH) ORDER BY day) sv GROUP BY id) v ON p.ID = v.id LEFT JOIN (SELECT comment_post_ID, COUNT(comment_post_ID) AS 'comment_count' FROM wp_16_comments WHERE comment_approved = 1 AND comment_date > DATE_SUB('2013-05-21 04:22:39', INTERVAL 1 MONTH) GROUP BY comment_post_ID ORDER BY comment_date DESC) c ON p.ID = c.comment_post_ID WHERE p.post_status = 'publish' AND p.post_password = '' AND v.pageviews > 0 AND p.post_type IN('post','page') GROUP BY p.ID ORDER BY v.pageviews DESC LIMIT 5;
- The topic ‘MySQL query performance issues’ is closed to new replies.