Slow database queries on archive

  • We are running a high traffic WordPress site with more than 65,000 posts.

    We are noticing the two following slow database queries whenever an archive page is loaded and they are causing a lot of server load.

    SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts 
LEFT JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE 1=1 
AND ( wp_term_relationships.term_taxonomy_id IN (11) )
AND wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private')
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 10

    and 

    SELECT wp_posts.ID
FROM wp_posts 
LEFT JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE 1=1 
AND ( wp_term_relationships.term_taxonomy_id IN (11) )
AND wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private')
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 10

    We are using a standard loop to call the latest 10 posts on archive.php:

    <?php if ( have_posts() ) : while ( have_posts() ) : the_post(); ?>

<article class="infinite-article-wide">
<div class="archive-image" ><a href="<?php the_permalink() ?>" rel="bookmark" ><div class="subhead-mini" ><? echo get_post_meta($post->ID, 'article-signpost', true) ?></div><img src="<? echo get_post_meta($post->ID, 'leadimage', true) ?>" alt="<?php the_title(); ?>"  /></a></div>
<div class="archive-data">
<a href="<?php the_permalink() ?>" rel="bookmark" >
<h2 class="archive-h2"><?php the_title(); ?></h2>
</a>
<h3 class="archive-h3"><? echo get_post_meta($post->ID, 'standfirst', true) ?></h3>
</div>
<div style="clear:both"></div>
</article>

<?php endwhile; else: ?>
<?php endif; ?>

    Is there a simple way to optimise these queries? Our archive pages just need to be set up to show the 10 latest posts from that category / tag and a link to the next page of posts.

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

  • @martincap,

    The reason why your page slows down is because of the function SQL_CALC_FOUND_ROWS. This function fetches the values from all the rows returned and that slows down your website, as far as my knowledge.

    If you do not have a specific use of the total number of rows, you should remove it. If you still need to know the total number of rows in wp_posts table (with the filters above), you should better be doing SELECT COUNT(*) FROM your_table

    References:
    https://stackoverflow.com/questions/3453809/how-to-use-mysql-found-rows-in-php
    https://dev.mysql.com/doc/refman/5.7/en/information-functions.html

    Thread Starter MartinCap

    (@martincap)

    Hi Maria, thanks very much for your reply.

    How can I remove the SQL_CALC_FOUND_ROWS function from the code above?

    Thanks,
    Martin

    @martincap,

    You can simply remove that function from your query. But before doing that, just check if you can find the SELECT FOUND_ROWS(); query. If yes, you have to replace that with SELECT COUNT(*) FROM your_table WHERE your_filters 

    
SELECT wp_posts.ID
FROM wp_posts 
LEFT JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE 1=1 
AND ( wp_term_relationships.term_taxonomy_id IN (11) )
AND wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private')
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 10

    Hope, this helps you.

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Slow database queries on archive’ is closed to new replies.