As WordPress developers, we often need to retrieve articles, pages, and other content that meet specific criteria from the WordPress database. In general, we don’t need to build SQL queries (usually we shouldn’t), because theWP_Queryclass and its methods provide us with a secure and efficient way to retrieve data from the database. We only need to declare an array of parameters, and the$queryobject builds the actual SQL query.
in this article, I’ll assume that you already know the basics of theWP_Queryclass, its methods and properties, and where to find a list of available variables.
We will focus on the parameters provided by theWP_Queryclass, which are specifically used to optimize SQL queries to reduce execution time and resource consumption.
when the traffic and content are limited, we usually don’t care about the efficiency of the query. 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 articles-then we have to consider query execution time.
- our toolbox
- WP_Query-why do we not count rows
- cache or do not cache fields returned by
our toolbox
the code I will show you has passed the Query Monitor test, this is a free plug-in Provides basic information about query performance, trigger hooks, HTTP requests, rewriting rules, and so on.
as an alternative to the plug-in, we can force WordPress to store query information and declare the following constant in wp-config.php:
define( 'SAVEQUERIES', true );
whenSAVEQUERIESis set totrue, WordPress registers the query and a pile of useful information in the$wpdb->queriesarray. Therefore, the name of the caller function and the execution interval of each query can be printed by adding the following code to a template file such as footer.php:
if ( current_user_can( 'administrator' ) ) {
global $wpdb;
echo '';
print_r( $wpdb->queries );
echo '
';
}
the following is an example of echoing content:
[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 want to delve deeper into this topic, please see our tutorial: edit wp-config.php. Finally, considering that plug-ins and built-inSAVEQUERIESfeatures are development tools, we should turn them off in a production environment. Having said that,
, let's take a look at how to speed up WordPress queries.
WP_Query-Why don't we count rows
We can query the database using theget_postsfunction, which returns an array of articles or a newWP_Queryobject instance. In both cases, we can determine the result of the query by setting the appropriate value for a specific variable.
lets start with an example that shows a common loop that 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();
$argsis a key / value pair array. These pairs are named query variables and determine or influence the actual SQL query. When querying the database from the plug-in, we may prefer to use thepre_get_postsfilter, 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 );
the important thing to note here is that the$queryobject is passed by reference, not by value, which means that the query parameters only affect the existing$queryinstance. The
setmethod adds a new query variable to the query specification and forces WordPress to retrieve all articles from thewebdevcategory. This is the result 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, theLIMITvalue has been set by the administrator user in the read options, as shown in the following figure.
in a custom query, we can set the number of rows thatposts_per_pagewill retrieve from the database because of the paging parameter. The
SQL_CALC_FOUND_ROWSoption forces the query to count the number of rows found. This number will be returned by the SQL functionFOUND_ROWS()as shown in the following example:
SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
WHERE id > 100 LIMIT 10;
SELECT FOUND_ROWS();
unfortunatelySQL_CALC_FOUND_ROWSsignificantly slows down query execution time. The good news is that we can force WordPress to remove options that provide underutilized (and undocumented)no_found_rowsvariables.
ifSQL_CALC_FOUND_ROWSis omitted,FOUND_ROWS()returns the number of lines with a maximum value ofLIMIT(more information on this topic in the MySQL documentation).
in a WordPress installation with hundreds of articles, the following meta-query takes 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
removesSQL_CALC_FOUND_ROWS, setsno_found_rowsto false, and the same query takes 0.0006 seconds.
thanks to the Query Monitor plug-in, we can easily compare two query
with and without the SQL_CALC_FOUND_ROWS option. When thewp_posttable contains thousands of rows, the query execution may take a few seconds. When we don't need paging, we should setno_found_rowstotrueto make the query run faster.
cached or uncached
WordPress provides a built-in caching system right out of the box. Although caching usually increases page loading speed, it may cause some additional queries to be run against the database. In addition, whenever a query is executed, a pile of unnecessary data may be requested. Fortunately for
, WordPress allows us to provide three specific parameters to disable caching:
- Cache_results: whether to cache article information. The default is true.
- Update_post_meta_cache: whether to update the article meta cache. The default is true.
- Update_post_term_cache: whether to update the article term cache. The default is true. If
has a persistent cache system enabled, such as Memcached, we don’t have to care about cache parameters, because WordPress sets these parameters to false by default.
in any other case, 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 );
Queries that return a small amount of data should not be cached when the permanent cache system is unavailable.
Fields returned by
as a general rule, we should never query the database for unnecessary fields. Field parameters provided by theWP_Queryclass, which allows you to restrict the ID or'id=>parent'fields of the returned field. The source document defines the fields parameter as follows: the field to be returned by
. A single field or all fields (strings), or an array of fields. Id=>parent’ uses’ id’ and ‘post_parent’. Default all fields. Accept “ids” and “id=>parent”.
The fields variable allows'ids'and'id=>parent', and defaults to * (any other value), although you will notice that WordPress sets this value to ids by default in multiple queries. Finally, we can optimize our first query:
true,
'update_post_meta_cache' => false,
'update_post_term_cache' => false,
'category_name' => 'cms',
'fields' => 'ids'
);
// The Query
$the_query = new WP_Query( $args );
$my_posts = $the_query->get_posts();
if( ! empty( $my_posts ) ){
foreach ( $my_posts as $p ){
// Your code
}
}
/* Restore original Post Data */
wp_reset_postdata();
?>
When a specific field is not needed, limit the returned field to ID.
Summary
takes into account that query speed may not be a huge advantage for small sites with hundreds of posts. If you want to prepare for growth, or if you are running a large Web site with expensive queries, you should optimize your WordPress queries. Inefficient queries can significantly slow down page loading, but with some simple adjustments, you can greatly speed up your site.
