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
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.