WordPress Performance Optimization Part 1: Know Your Database Queries!

May 26, 2016

There are many bad habits that WordPress developers should avoid. Some of these include using asterisk (*) for SELECT queries, redundant queries, and most importantly not being familiar enough with SQL.

After years of developing on enterprise websites, developers often ask themselves these following questions:

  • Why did I not know this method before?
  • How was I able to get by before?

Most developers who work on WordPress will acknowledge that WordPress has:

  • Consistent optimization issues
  • Scalability issues that can cause site outages
  • Third-party plugins that create high database loads

It is a common practice for a WordPress developer to limit the amount of plugins installed and activated on their website. The overhead a bad plugin can inflict on a website is unimaginable. When you rely on third-party plugins, the performance of your website is at the mercy of those third-party developers. If you don’t have an understanding of the code in the plugin, the plugin could contain an inefficient query that could potentially take down your website. Inefficient plugins are especially difficult to track down, and it could take weeks to figure out the cause of your site slow-down.

The subject of performance tuning can be a very broad topic to cover. Instead of tackling it all at once, I will attempt to address a pagination query that has created scalability problems for many WordPress developers. This is a query that we often see when developing on a WordPress site.

Instead of giving out the solution immediately, let’s do this exercise together.

Take a moment and identify 10 things that you can optimize for this following query:

SELECT SQL_CALC_FOUND_ROWS wp_posts.*, wp_postmeta.*
FROM wp_posts
INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
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 (554) ) 
AND wp_posts.post_type = 'post' 
AND (wp_posts.post_status != 'draft' || wp_posts.post_status <> 'private' || wp_posts.post_status != 'trash')
AND CAST(wp_postmeta.meta_value AS CHAR) = 'episodes'
GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_date DESC 
LIMIT 0, 10;
SELECT FOUND_ROWS();

Here is a better approach:

  • 1. wp_posts.* => wp_posts.post_title, wp_posts.post_content, wp_posts.post_name, wp_posts.post_date, wp_postmeta.meta_key, wp_postmeta.meta_value 

    List only the columns that you need. The more columns you listed, the larger your object becomes for memory.

  • 2. WHERE 1=1 will always be true. 

    This is a redundant clause. I have always followed the philosophy of keeping things short and simple! Follow the KISS principle. If you’re building a WHERE clause on the fly and you’re unsure if you need additional expressions in the WHERE clause, a 1=1 at the end ensures that you’ll create a valid WHERE clause so that the SELECT statement won’t blow up.

  • 3. wp_term_relationships.term_taxonomy_id IN (554) 

    For this specific statement – there is only 1 taxonomy being included. Using ‘=’ directly is the best for performance, followed by IN. If there are more than 2 taxonomies to call, use IN. OR is the slowest.

  • 4. (wp_posts.post_status != ‘draft’ || wp_posts.post_status <> ‘private’ || wp_posts.post_status != ‘trash’) 

    != AND <> are both terrible for performances, you can accomplish the same results by using wp_posts.post_status = ‘publish’.

  • 5. CAST(wp_postmeta.meta_value AS CHAR) = ‘episodes’ 

    This one is tricky because meta_value is a LONGTEXT type and is not indexed by the database schema. Using this key/value pair is slightly resource heavier than meta_key or post_id in the wp_postmeta table.

  • 6. GROUP BY wp_posts.ID 

    Using GROUP BY can be performance intensive. Imagine the sorting algorithm that needs to be executed in order to get the proper return dataset.

  • 7. ORDER BY wp_posts.post_date DESC. 

    ASC is the default ORDER BY clause. DESC is basically reversing the order chronologically, requiring additional algorithm to be performed.

  • 8. LIMIT 0, 10 

    Using the offset method can be lousy for performance. MySQL is typically inadequate for high offsets. Imagine the scenario LIMIT 200000, 10. This can be problematic when you paginate over a full table storing all the rows in memory. Consider encapsulating the query to allow the system to read a few rows at a time. One solution is to use an indexed “auto-incrementing” ID as an alternative.

  • 9. ‘OR’ is moderately faster in a fraction of a second when comparing to ‘||’. Another benefit is readability.
  •  

  • 10. SELECT FOUND_ROWS()

    Executing needless queries. This could have been accomplished with a single efficient query.

I hope you enjoyed this first part of the WordPress performance series. Stay tune for part two.

Digital at The WNET Group is not responsible for your or any third party’s use of code from this tutorial. All the information on this website is published in good faith “as is” and for general information purposes only; WNET and IEG make no representation or warranty regarding reliability, accuracy, or completeness of the content on this website, and any use you make of this code is strictly at your own risk.