Optimize Wordpress Query that take 5 seconds to execute

Question

I have a large wordpress database:

rows in key tables:

730K wp_posts
404K wp_terms
752K wp_term_relationships
27K wp_term_taxonomy
1.8 Million wp_postmeta

The issue is that I have a query that takes 5 seconds to complete and I want to optimize the query before adding any caching.

mysql> SELECT wp_posts.ID
 FROM wp_posts
 INNER JOIN wp_term_relationships
 ON (wp_posts.ID = wp_term_relationships.object_id)
 LEFT JOIN wp_postmeta
 ON (wp_posts.ID = wp_postmeta.post_id
 AND wp_postmeta.meta_key = '_Original Post ID' )
 LEFT JOIN wp_postmeta AS mt1
 ON ( wp_posts.ID = mt1.post_id )
 WHERE 1=1
 AND wp_posts.ID NOT IN (731467)
 AND ( wp_term_relationships.term_taxonomy_id IN (5) )
 AND wp_posts.post_type = 'post'
 AND (wp_posts.post_status = 'publish'
 OR wp_posts.post_status = 'private')
 AND ( wp_postmeta.post_id IS NULL 
 OR ( mt1.meta_key = '_Original Post ID'
 AND CAST(mt1.meta_value AS CHAR) = 'deleted' ) )
 GROUP BY wp_posts.ID
 ORDER BY  wp_posts.ID DESC
 LIMIT 0, 20;

Here is the results:

+--------+
| ID     |
+--------+
| 731451 |
| 731405 |
| 731403 |
| 731397 |
| 731391 |
| 731385 |
| 731375 |
| 731363 |
| 731361 |
| 731353 |
| 731347 |
| 731345 |
| 731335 |
| 731331 |
| 731304 |
| 731300 |
| 731284 |
| 731273 |
| 731258 |
| 731254 |
+--------+

Doing an explain on the query yields the following information

+----+-------------+-----------------------+--------+------------------------------------------------------------+------------------+---------+----------------------------------------+--------+-----------------------------------------------------------+
| id | select_type | table                 | type   | possible_keys                                                 | key              | key_len | ref                                    | rows   |   Extra                                                     |
+----+-------------+-----------------------+--------+------------------------------------------------------------+------------------+---------+----------------------------------------+--------+-----------------------------------------------------------+
|  1 | SIMPLE      | wp_term_relationships | range  | PRIMARY,term_taxonomy_id                                      | term_taxonomy_id | 16      | NULL                                   | 130445 |   Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | wp_posts              | eq_ref | PRIMARY,post_name,type_status_date,post_parent,post_author | PRIMARY          | 8       | mydatabase.wp_term_relationships.object_id |      1 | Using where                                               |
|  1 | SIMPLE      | wp_postmeta           | ref    | post_id,meta_key                                           | post_id          | 8       | mydatabase.wp_term_relationships.object_id |      1 | Using where                                               |
|  1 | SIMPLE      | mt1                   | ref    | post_id                                                    | post_id          | 8       | mydatabase.wp_term_relationships.object_id |      1 | Using where                                               |
+----+-------------+-----------------------+--------+------------------------------------------------------------+------------------+---------+----------------------------------------+--------+-----------------------------------------------------------+

How can I optimize this query to load faster? I thinking a custom index would be the way to go but not sure on which fields. Also I tried to order the results wp_posts.ID DESC but get the same time to execute the query.

Answer 1

I had the exact same issue. The problem is not one that can be fixed without modifying some code that you probably shouldn't (or perhaps writing a filter or a 'drop-in'). The issue is the CAST directive in the SQL statement. It CASTS the entire table before it does anything, with the amount of records you have, its going to take a while.

Capture the query, remove the following "AND CAST(mt1.meta_value AS CHAR) = 'deleted'" and run it, it should be a lot quicker now.

Edit: (Correction) change the query to "AND mt1.meta_value = 'deleted'"

I have no idea what the developers were thinking when they added that useless CAST, with MySQL it works fine without it (TEXT is no different than CHAR except the size). I am sure there are some edge cases where removing it will not give the desired results, but I have yet to find one.

Long live Wordpress SQL X)

Answer 2

So what I'm going to do going forward if I use wordpress for this project is to create a reverse post id index. I don't think this is the "correct" answer and some people will definitely outright disagree with this approach but this is working for me in production.

I got the idea from reading this blog post here:

https://www.igvita.com/2007/08/20/pseudo-reverse-indexes-in-mysql/

As I recently discovered, MySQL currently only supports storage of index values in ascending order....It took all of three weeks for AideRSS to hit the 3 million plus indexed blog posts, and in the process I could feel the site getting more sluggish: the descending order by clause was killing us. In the worst case, merging a union of several queries meant the performance hit of a filesort operation!

Because of this limitation with mysql, the following order & sorting functions are the bottleneck in the query.

GROUP BY wp_posts.ID
ORDER BY  wp_posts.ID DESC

However removing those conditions the query will return in 20ms from an average of 5-15 seconds in production. However the issue is that the posts are ordered by oldest to newest. What I want is newest to oldest.

SELECT wp_posts.*
FROM wp_posts
WHERE wp_posts.ID IN( 
SELECT distinct(ID) 
FROM wp_posts  
INNER JOIN wp_term_relationships 
ON (wp_posts.ID = wp_term_relationships.object_id) 
LEFT JOIN wp_postmeta 
ON (wp_posts.ID = wp_postmeta.post_id 
AND wp_postmeta.meta_key = '_Original Post ID' ) 
LEFT JOIN wp_postmeta AS mt1 
ON ( wp_posts.ID = mt1.post_id ) 
WHERE wp_posts.ID NOT IN (795025) 
AND ( wp_term_relationships.term_taxonomy_id IN (1) ) 
AND wp_posts.post_type = 'post' 
AND (wp_posts.post_status = 'publish' 
OR wp_posts.post_status = 'private') 
AND ( wp_postmeta.post_id IS NULL 
OR ( mt1.meta_key = '_Original Post ID' 
AND CAST(mt1.meta_value AS CHAR) = 'deleted' ) ) ) limit 0, 20;

So again going back to this post here: https://www.igvita.com/2007/08/20/pseudo-reverse-indexes-in-mysql/

Following Peter Zaitsev's advice on faking a reverse index, I decided to sidestep our problem by creating a separate reverse timestamp for the publication time of an indexed blog post. The trick is, since all indexes are stored in ascending order, instead of storing the publication date, you need to store a 'countdown' value from some date in the future. A few SQL queries will do the trick:

Instead of storing the post creation date "reversed", I decided to store the Post ID in a negative format in the wp_posts table.

So I added in mysql in my wordpress database an additional column to the wp_posts table. This new column stores a int negative number.

alter table wp_posts add column reverse_post_id int;

Update current posts to have this reverse number for new column:

 update wp_posts set reverse_post_id = (ID/ -1);

I then create an index on this new reverse_post_id:

  create index reverse_post_id_index on wp_posts(post_type,post_status,reverse_post_id);

Currently I insert posts programmatically via an custom api interface so I create the reverse post id after insertion. I will be adding a hook to create the reverse_post_id in wordpress after inserting a post through the interface.

Im also going to add a mysql scheduled event to run at some interval to update wp_posts where reverse_post_id is null.

The final query looks like this and runs in under 20 ms or less in production:

SELECT wp_posts.*
FROM wp_posts
WHERE wp_posts.ID IN( 
SELECT distinct(ID) 
FROM wp_posts **force index (reverse_post_id_index)** 
INNER JOIN wp_term_relationships 
ON (wp_posts.ID = wp_term_relationships.object_id) 
LEFT JOIN wp_postmeta 
ON (wp_posts.ID = wp_postmeta.post_id 
AND wp_postmeta.meta_key = '_Original Post ID' ) 
LEFT JOIN wp_postmeta AS mt1 
ON ( wp_posts.ID = mt1.post_id ) 
WHERE wp_posts.ID NOT IN (795025) 
AND ( wp_term_relationships.term_taxonomy_id IN (1) ) 
AND wp_posts.post_type = 'post' 
AND (wp_posts.post_status = 'publish' 
OR wp_posts.post_status = 'private') 
AND ( wp_postmeta.post_id IS NULL 
OR ( mt1.meta_key = '_Original Post ID' 
AND CAST(mt1.meta_value AS CHAR) = 'deleted' ) ) ) limit 0, 20;

Notice the addition of "force index (reverse_post_id_index)" this returns the wp_posts in desc order newest to oldest without "order by" operation. The caveat is that the reverese_post_id can not be null.

Again this is probably not the correct answer but the answer I found to make it work for me and my situation.