Building Efficient WordPress Queries with WP_Query

Updated on June 05, 2017

As WordPress developers, we often need to retrieve posts, pages and other content matching specific criteria from the database. Usually, we don’t need to build SQL queries (and often we shouldn’t) because the WP_Query class and its methods provide us with a safe and efficient way to retrieve data from the database. We just need to declare an array of arguments, and the $query object will build the actual SQL query.

In this post, I will assume you already know the basics of the WP_Query class, its methods and properties, and where to find a list of the available variables.

I will focus on parameters provided by the WP_Query class specifically to optimize SQL queries, reducing execution time and resource consumption.

When traffic and content are limited, we usually don’t care about the efficiency of our queries. WordPress builds well-optimized SQL queries and provides a caching system out of the box.

When traffic and site content grow significantly – up to thousands of posts – then we must consider query execution time.

Our toolbox

The code I’m going to show you has been tested with Query Monitor, a free plugin which provides essential information about query performance, triggered hooks, HTTP requests, rewrite rules, and much more.

Alternatively to a plugin, we can force WordPress to store query information declaring the following constant in wp-config.php:

define( 'SAVEQUERIES', true );

When SAVEQUERIES is set to true, WordPress registers the queries and a bunch of useful information in the $wpdb->queries array. So, the names of caller functions and the execution lapse of each query can be printed by adding the following code in a template file like footer.php:

if ( current_user_can( 'administrator' ) ) {
	global $wpdb;
	echo '<pre>';
	print_r( $wpdb->queries );
	echo '</pre>';
}

Here is an example of what is echoed:

[4] => Array
(
	[0] => SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')  ORDER BY wp_posts.post_date DESC LIMIT 0, 10
	[1] => 0.0163011550903
	[2] => require('wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts, QM_DB->query
	[trace] => QM_Backtrace Object
		( ... )
	[result] => 10
)

If you’d like to dive deep into this topic, take a look at our tutorial: Editing wp-config.php.
Finally, consider that both the plugin and the built-in SAVEQUERIES functionality are developing tools that we should switch-off in a production environment.

That being said, let’s have a look at how to speed up WordPress queries.

WP_Query – Why we’d not count rows

We can query the database with the get_posts function, which returns an array of posts, or a new instance of WP_Query object. In both cases we can determine the results of the queries by setting appropriate values to specific variables.

Let’s start with an example which shows a common Loop as it usually appears in a template file:

// The Query
$the_query = new WP_Query( $args );
// The Loop
if ( $the_query->have_posts() ) {
	while ( $the_query->have_posts() ) : $the_query->the_post(); 
		// Your code here
	endwhile;
} else {
		// no posts found
}
/* Restore original Post Data */
wp_reset_postdata();

$args is an array of key/value pairs. These pairs are named query vars, and determine or affect the actual SQL query.
When querying the database from a plugin, we may prefer to use the pre_get_posts filter, as shown in the following example:

function myplugin_pre_get_posts( $query ) {
  if ( is_admin() || ! $query->is_main_query() ){
	return;
  }
  $query->set( 'category_name', 'webdev' );
}
add_action( 'pre_get_posts', 'myplugin_pre_get_posts', 1 );

An important thing to notice here is that the $query object is passed by reference, not by value, meaning that the query arguments are just affecting an existing $query instance.

The set method adds a new query var to the query specification and will force WordPress to retrieve all posts from webdev category. This is the resulting query:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts 
INNER JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE 1=1 
AND ( wp_term_relationships.term_taxonomy_id IN (12) )
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

In this example, the LIMIT value has been set by the admin user in Reading options, as shown in the image below.

Reading Screen

In custom queries we can set the number of rows to be retrieved from the database thanks to the pagination parameter posts_per_page.

The SQL_CALC_FOUND_ROWS option forces the query to count the number of found rows. This number will be returned by the FOUND_ROWS() SQL function, as shown in the following example:

SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
WHERE id > 100 LIMIT 10;

SELECT FOUND_ROWS();

Unfortunately, SQL_CALC_FOUND_ROWS can significantly slow down the query execution time.
The good news is that we can force WordPress to remove the option providing the under-used (and not documented) no_found_rows variable.

If SQL_CALC_FOUND_ROWS is omitted, FOUND_ROWS() returns the number of rows up to the value of LIMIT (more on this topic on MySQL documentation).

In a WordPress install with few hundreds of posts, the following meta query took 0.0107 seconds:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts 
INNER JOIN wp_postmeta
ON ( wp_posts.ID = wp_postmeta.post_id )
WHERE 1=1 
AND ( ( wp_postmeta.meta_key = 'book_author'
AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '%Isaac Asimov%' ) )
AND wp_posts.post_type = 'book'
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

Removing SQL_CALC_FOUND_ROWS setting no_found_rows to false, the same query took 0.0006 seconds.

Thanks to Query Monitor plugin, we can easily compare two queries enabling and disabling SQL_CALC_FOUND_ROWS option

Thanks to Query Monitor plugin, we can easily compare two queries with and without SQL_CALC_FOUND_ROWS option

When the wp_post table contains thousands of rows, the query execution could take seconds.
When we don’t need pagination, we should ever set no_found_rows to true, making the query run dramatically faster.

To cache or not to cache

WordPress provides a built-in caching system out of the box. Although caching generally improves page load speed, it may cause some extra queries to be run against the database. Additionally, anytime a query is executed a bunch of unnecessary data could be requested.

Fortunately, WordPress allows us to disable caching providing three specific parameters:

  • cache_results: Whether to cache post information. Default true.
  • update_post_meta_cache: Whether to update the post meta cache. Default true.
  • update_post_term_cache: Whether to update the post term cache. Default true.

If a persistent caching system is enabled, such as Memcached, we don’t have to care about caching parameters because WordPress will set to false these arguments by default.

In any other situation, we can build a faster query with the following code:

function myplugin_pre_get_posts( $query ) {
  if ( is_admin() || ! $query->is_main_query() ){
	return;
  }
  $query->set( 'category_name', 'webdev' );

  $query->set( 'no_found_rows', true );
  $query->set( 'update_post_meta_cache', false );
  $query->set( 'update_post_term_cache', false );
}
add_action( 'pre_get_posts', 'myplugin_pre_get_posts', 1 );

When a permanent caching system is not available, queries returning small amounts of data should not be cached.

Returned fields

As a general rule, we should never query the database for unnecessary fields. The WP_Query class provides the fields argument, which allows to limit the returned fields to the IDs or 'id=>parent' fields. The source file documentation define the fields argument as follows:

Which fields to return. Single field or all fields (string), or array of fields. ‘id=>parent’ uses ‘id’ and ‘post_parent’. Default all fields. Accepts ‘ids’, ‘id=>parent’.

The fields variable admits 'ids' and 'id=>parent', and defaults to * (any other value), although you’ll notice that by default WordPress will set the value to ids in several queries.
Finally, we can optimize our first query:

<?php
$args = array( 
	'no_found_rows' => true, 
	'update_post_meta_cache' => false, 
	'update_post_term_cache' => false, 
	'category_name' => 'cms', 
	'fields' => 'ids'
);
// The Query
$the_query = new WP_Query( $args );
// The Loop
if ( $the_query->have_posts() ) {
	?>
	<ul>
	<?php while ( $the_query->have_posts() ) : $the_query->the_post(); ?>
		<li><a href="<?php the_permalink(); ?>" rel="bookmark" title="Permanent Link to <?php the_title_attribute(); ?>"><?php the_title(); ?></a></li>
	<?php endwhile; ?>
	</ul>
	<?php
} else {
	// no posts found
}
/* Restore original Post Data */
wp_reset_postdata();
?>

When specific fields are not required, limit the returned fields to IDs.

Conclusions

Considering query speed may not bring huge advantages for small websites with a few hundred posts. If you want to get ready for growth or you are running a large website with expensive queries you should optimize your WordPress queries. Inefficient queries can dramatically slow down page loads but with a few simple tweaks you can speed up your website considerably.

This article was written by Carlo Daniele

Carlo is a freelance front-end designer and developer. When he writes articles and tutorials, Carlo mainly deals with web standards, but when he plays with websites his best workmate is WordPress.

Hand-picked related articles

Leave a Reply

Use WordPress?

Join 20,000+ others who get our FREE weekly newsletter with WordPress tips on how to drive more traffic and revenue to your business!

You have Successfully Subscribed!

Send this to a friend