WordPress is fast and easy to work with even for more complicated projects. Most of the time working with WordPress you do not need to do MySQL query optimization, that is one of the benefits of how WordPress is structured. However, sometimes you need to step it up if you are looking for speed and better performance.

The flexibility and built in helper functions make WordPress a very flexible platform for many things. In these days where speed is essential for user experiences and SEO, tweaking and optimizing custom WordPress installations is important.

I have written optimization tips before, header.php optimization tips, but not much about MySQL query optimization. A recent problem on a project took me deep down into custom MySQL queries for pulling in custom fields and other meta data. Code example below.

Why bother doing MySQL query optimization?

A recent project involved listing products categorized by a custom taxonomy. I created a helper function to output each product. All processing and HTML rending of each product is handled by that function. I basically created a custom WP_Query and looped through each product (a custom post type) sending the post ID along to the function.

<?php function proddisplay($postid) { $details=get_post($postid); // etc... } ?>

Easy and fast to make global changes, but not very efficient. In that function I had code that looked up several details for the product, the permalink, the featured image, custom fields, etc. Each of these WordPress functions need extra processing time and at least one database query. As I was displaying 35 products at a time, it easily lead to a lot of extra queries. Before I started optimizing, a category page used on average 236 database queries.

How to optimize that?

So, I wrote down a list of what details I needed for each product and set out to see if I could pull all details required for the product list with just one MySQL query. It turns out you can, and the query runs fast. The following query does several things.

  • It returns the id of the product (post)
  • It returns the price (custom field)
  • It returns the name of the product (custom field)
  • It returns the url of the featured image
$productsquery= "
SELECT p.ID,
pm1.meta_value AS price,
pm3.meta_value as name,
wm2.meta_value as imgurl
FROM $wpdb->term_relationships t
INNER JOIN $wpdb->posts p ON t.object_id = p.ID
LEFT JOIN $wpdb->postmeta pm1 ON (
pm1.post_id = p.ID AND
pm1.meta_key = 'affdom2_price'
) LEFT JOIN $wpdb->postmeta pm3 ON (
pm3.post_id = p.ID AND
pm3.meta_key = 'affdom2_name'
) LEFT JOIN $wpdb->postmeta wm1 ON ( wm1.post_id = p.ID
AND wm1.meta_value IS NOT NULL
AND wm1.meta_key = '_thumbnail_id' )
LEFT JOIN $wpdb->postmeta wm2 ON ( wm1.meta_value = wm2.post_id
AND wm2.meta_key = '_wp_attached_file'
AND wm2.meta_value IS NOT NULL )

WHERE p.post_type ='products'
AND p.post_status = 'publish'

GROUP BY ID
ORDER BY ID ASC
LIMIT 15
";

That MySQL query returns a nice array containing just the details that I need and nothing else.

Read also  WordPress Optimization Checklist


Now I can just parse each product as an object and just use the proddisplay() function for displaying.

Implementing this reduced queries, load time and server load in general almost instantly, but it is not easily done.

So, did it help?

It did. On average each category page got down to about 97 database queries on average.But my optimization did not stop there 🙂

Tip: You can also add pagination to above query by using custom “LIMIT” values in the MySQL query 😉

Bonus:

You can tweak above query to also filter by a custom taxonomy or category. Just add

AND t.term_taxonomy_id = 'the tax id of the term'

to above MySQL query.

I wish I could take full credit for that query. It is a hack and mix from many sources, and the credits got lost in the development 🙁 If you recognize some part of the code that should be credited, please let me know and I will update asap.

Error: Please enter a valid email address

Error: Invalid email

Error: Please enter your first name

Error: Please enter your last name

Error: Please enter a username

Error: Please enter a password

Error: Please confirm your password

Error: Password and password confirmation do not match