WordPress Performance Optimization Part 1: Know your database queries

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

After years of developing on corporate websites, developers often ask themselves the following questions:

• Why did I not know this method before?
• How could I get away with it before?

Most developers working on WordPress will recognize that WordPress has:

• Constant optimization problems
• Scalability issues that can cause site downtime
• Third party plugins that create high database loads

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

The topic of performance optimization can be a very broad topic to cover. Instead of tackling everything at once, I'll try to tackle a pagination query that has created scalability issues for many WordPress developers. This is a query we often see when developing on a WordPress site.

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

Take a moment and identify 10 things 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's 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_valueList only the columns you need. The more columns you have listed, the larger your memory object becomes.
  • 2. WHERE 1 = 1 will always be true. This is a redundant clause. I've always followed the philosophy of keeping things short and simple! Follow the KISS principle. If you are building a clause WHERE on the fly and are not sure if you need additional expressions in the clause WHERE, a 1 1 = at the end it ensures that you will create a clause WHERE so that the statement SELECT  does not explode.
  • 3. wp_term_relationships.term_taxonomy_id IN (554)For this specific statement, only 1 taxonomy is included. Direct use say '='  is 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 <> they are both terrible for performance, you can get the same results using wp_posts.post_status = 'publish' .
  • 5. CAST (wp_postmeta.meta_value AS CHAR) = 'episodes'This is complicated because meta_value it's a guy LONGTEXT and is not indexed by the database schema. Using this key / value pair is slightly heavier in terms of resources than the meta_key o post_id in the table wp_postmeta .
  • 6. GROUP BY wp_posts.IDThe use of GROUP BY may require high performance. Imagine the sorting algorithm that needs to be done to get the correct return dataset.
  • 7. ORDER BY wp_posts.post_date DESC.ASC is the clause ORDER BY default. The DESC it is essentially reversing the order chronologically, requiring the execution of an additional algorithm.
  • 8. LIMIT 0, 10The use of the method offset can be bad for performance. MySQL is generally inadequate for offset high. Imagine the scenario  LIMIT 200000, 10 . This can be problematic when paging over an entire table by storing all rows in memory. Consider encapsulating the query to allow the system to read a few lines at a time. One solution is to use an autoincrement indexed ID as an alternative.
  • 9. 'OR' it is moderately faster in a split second than '||'. Another advantage is the readability. 
  • 10. SELECT FOUND_ROWS ()Unnecessary query execution. This could have been achieved with a single efficient query.

I hope you enjoyed this first part of the WordPress performance series. Stay tuned for the second part.