May 27, 2022

WordPress Performance Optimization Part 1: Know your database queries

How to Optimize the Most Challenging Queries on WordPress?

SQL Banner
Print Friendly, PDF & Email

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.

Do you have doubts? Not sure where to start? Contact us


We have all the answers to your questions to help you make the right choice.

Chat with us

Chat directly with our presales support.

0256569681

Contact us by phone during office hours 9:30 - 19:30

Contact us online

Open a request directly in the contact area.

PRACTICAL

ManagedServer.it is the leading Italian provider of high performance hosting solutions. Our subscription model is affordable and predictable, so customers can access our reliable hosting technologies, dedicated servers and the cloud. ManagedServer.it also offers excellent support and consulting services on Hosting of the main Open Source CMS such as WordPress, WooCommerce, Drupal, Prestashop, Magento.

JUST A MOMENT !

Would you like to see how your WooCommerce runs on our systems without having to migrate anything? 

Enter the address of your WooCommerce site and you will get a navigable demonstration, without having to do absolutely anything and completely free.

No thanks, my customers prefer the slow site.
Back to top