Extreme MySQL query optimization for WordPress

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.


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 🙂

[box type=”info”]

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

[/box]

[box type=”info”]

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.

[/box]

[box type=”info”] 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.[/box]

3 Comments

  1. Anonymous on 29/08/2012 at 07:44

    Your post is really useful for me. Thanks a lot for this post.

  2. Peter on 13/09/2012 at 11:46

    Thanks a lot for your code snippet. Really helpful for me.

    • Lars Koudal on 21/10/2012 at 10:55

      Glad it helped you Peter. MySQL can be a bit tricky, but worth it going down from 236 to 97 database queries. It can be even more optimized of course…



Leave a Comment





Cleverplugins.com Newsletter

Articles about how to improve your WordPress website, how to get more traffic and most importantly more customers.

Subscribe

Something went wrong. Please check your entries and try again.