WordPress queries can quickly become resource intensive if you are handling many meta and taxonomy queries alongside your queries. Running a WP_Query() along with get_terms() and get_post_meta() for each query item builds up; though the flexibility of being able to bring that data into frontend designs is appealing. The more complicated your designs get with multiple custom taxonomies and meta fields are taxing on the server and, in the case of shared hosting, can get you flagged for high server usage and possibly have your site shut down until you can address the issue.
To start with, if you are reading this and don’t understand how MySQL works then I would recommend a primer on how a database driven application works from the database to the browser. In short: WordPress is saves it’s posts to a database and gets data from the database in queries. Every request for data is a query and queries can be as small as only checking if an post ID exists or as large as getting all posts and all associated meta fields. PHP, the language WordPress is built on, connects to the database, makes queries, and returns that data to the frontend for the browser to render to users. That’s a very short, rough description of how WordPress and MySQL work together.
Testing for Query usage
To assist in testing and debugging database queries, WordPress has a nice function for us: $wpdb->num_queries(). This function returns the number of queries being requested from the WordPress database, hence being part of the $wpdb global object. I prefer to use this in conjunction with another one of WordPress’ functions: timer_stop() which returns the time it took to render the current page. When combined these functions can give you a mini page-load diagnostic while you are developing and debugging your theme or plugin.
WordPress Database Structure
WordPress, like most MySQL driven applications, is relational: meaning that a majority of rows are related to another table – not a single row contains everything you need. For example: the _posts table contains essential post info but does not contain meta fields (_postmeta) or taxonomy terms (_terms, _term_relationships, _term_taxonomy). The standard WordPress tropes to get your posts, wp_query() and get_posts() returns just your post data from the _posts table, in order to get meta and taxonomy terms you would need an additional two queries per loop/foreach.
With the previous statement, if you were to query 10 posts and the corresponding terms and meta in bulk, using get_object_terms() and get_post_meta() respectively, that would equate to 1 + (10 * 2) queries: 21 queries or your posts and related terms. Now that assumes your are bulk querying your terms and meta fields as opposed to querying them individually, which could exponentially increase your database usage.
WordPress Bulk Querying
On a recent project and a few in the past, as relationships for meta and taxonomies got larger and the databases themselves got larger I ran into a few CPU spikes that caused issues on shared hosting services. The most recent instance was a 2k+ post database, querying about 90 items with 3 associated taxonomies and about 20 meta fields a piece. Alone the usage was about 198 queries, causing a huge issue with processor usage. After some digging into the problem with New Relic and testing with the $wpdb->num_queries() function I decided the best route would be to ignore WordPress’ post queries and instead create a new bulk query to replicate get_posts(), but also to retrieve the associated meta fields and taxonomy terms in one big query, processing the data after the query with a collection of helper functions to keep functions uniform and simple throughout the theme.
In the end, I was able to reduce 198~ queries in an archive to about 70~ queries to return the same data in a bulk format.
I achieved this through a collection of functions to get posts as well as process the taxonomies (during concatenation I could only merge that so much):
- get_posts_terms_fields($args) to get multiple posts. This function accepts the same argument array as get_posts().
- get_post_terms_fields($args) to get one post. This function accepts the same argument array as get_posts().
- get_object_taxonomy($obj->taxonomy, $taxonomy_name) to get an array of a single taxonomy from the $obj->taxonomy array returned from get_posts_terms_fields() or get_post_terms_fields().
- get_implode_taxonomy($obj->taxonomy, $field) to get a comma separated string of fields from a taxonomy array created by get_object_taxonomy().
If you are interested in the functions used to make this happen, check out the Gist at the end of this post.
Other Performance Methods
This all depends on your caching and hosting situation, but if performance is important to you, and in a shared hosting environment it should be, then trying to reduce your query usage will reduce your server’s CPU usage and save you a lot of trouble in the future.
Another way to improve your WordPress site’s database performance is to optimize the database. The longer your site exists and the more posts are made on the site then you’ll need to optimize the database, much like defragging a harddrive. There are many plugins that can achieve this for you or you can do this through PhpMyAdmin, a MySQL GUI included with many hosting services. You can do this easily by choosing to Optimize Table from the structure view of your database:
*be sure to backup your database first before performing any database operations!
Optimizing your database is an easy and important part of WordPress maintenance that should be in your long term upkeep plan.