MySQL query performance issues

  • Jay Smooth

    (@jsmooth1200)


    12 years, 12 months ago

    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;

    http://ww.wp.xz.cn/extend/plugins/wordpress-popular-posts/

Viewing 2 replies - 1 through 2 (of 2 total)
  • Plugin Author Hector Cabrera

    (@hcabrera)

    12 years, 12 months ago

    Hi there,

    The plugin does make some heavy queries to the database to update the popular posts list on-the-fly, causing some performance issues on large sites. From version 2.3.2 and on, I’ve been working on improving the queries to improve the speed – but the truth is there’s still a lot of work to do.

    I already have in mind a couple of solutions that should lower the impact on the database, problem is I’m a little short of time at the moment. For now, if you’re ordering your popular lists by views disabling comments count should be good enough (and viceversa).

    Thread Starter Jay Smooth

    (@jsmooth1200)

    12 years, 12 months ago

    Hi Héctor; thanks for the quick response! I’ve disabled the comments, so we’ll see how that helps. I’ll report back in a month or so. I appreciate the effort you’ve put into this plugin and look forward to using future iterations of it. Take care, and thanks!

Viewing 2 replies - 1 through 2 (of 2 total)

The topic ‘MySQL query performance issues’ is closed to new replies.