SQL_CALC_FOUND_ROWS Sever load issues

  • Resolved MartinCap

    (@martincap)


    8 years, 3 months ago

    We have been having severe server load issues, which our hosts say are because of the following SQL query.

    | 27914 | thesport_cmsnew | localhost | thesport_repaired | Query | 7 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts |
| 27916 | thesport_cmsnew | localhost | thesport_repaired | Query | 15 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts |
| 27920 | thesport_cmsnew | localhost | thesport_repaired | Query | 15 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts |
| 27922 | thesport_cmsnew | localhost | thesport_repaired | Query | 14 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts |

    I’m struggling to find out what is causing the issue.

    Our site has a big database of more than 60,000 posts and we get around 2m users a month.

    We have tried optimizing the database and making sure the server has enough resources, but the problem has got so bad now that we cannot publish posts without the load going through the roof.

    We have W3TC installed using Memcached.

    Active plugins:

      CloudFlare
      Co-Authors Plus
      Custom Field Template
      Facebook Page Promoter Lightbox Premium
      Select Google Ads in content
      Google Ads in content
      Select Heartbeat Control
      Heartbeat Control
      Infinite Scroll
      Jetpack by WordPress.com
      Post Editor Buttons Fork
      Post Snippets
      RSS Filter
      VaultPress
      W3 Total Cache
      WordPress Popular Posts
      WPShare Counter
      Yoast SEO
      Yoast SEO: News
      YOURLS Link Creator
      Zone Manager (Zoninator)

    Any ideas would be much appreciated.

Viewing 1 replies (of 1 total)
  • Thread Starter MartinCap

    (@martincap)

    Just to follow up on this for anyone who may be reading and facing similar issues, we seem to have fixed the problem.

    The issue (which had been ongoing for some time but for some reason only caused problems sporadically) appears to have been caused by the SQL_CALC_FOUND_ROWS query causing a server-resource bottleneck.

    Hundreds of the same SQL_CALC_FOUND_ROWS query would run and cause a big strain on server resources, often rendering WordPress completely unusable. Some times it would last a few minutes, other times it would last hours.

    After going through our custom theme’s code, we realised that all of the post pages were using the (inefficient) code below to load recent posts:

    <?php $recent = new WP_Query(array('cat' => 11, 'showposts' => 14 )); while($recent->have_posts()) : $recent->the_post();?>

    As we did not require pagination in this instance, we simply changed this to include the ‘no_found_rows’ => true part (to prevent the pesky SQL_CALC_FOUND_ROWS query altogether) and it seems to have solved our woes.

    So the same code now looks like this:

    <?php $recent = new WP_Query(array('no_found_rows' => true, 'cat' => 11, 'showposts' => 14 )); while($recent->have_posts()) : $recent->the_post();?>

    I’m by no means an expert when it comes to this stuff but I hope it will be useful to someone if they face similar issues.

    Here is where we found the fix: https://thomasgriffin.io/optimize-wordpress-queries/

Viewing 1 replies (of 1 total)
  • The topic ‘SQL_CALC_FOUND_ROWS Sever load issues’ is closed to new replies.