Optimize WordPress Query that take 5 seconds to execute

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.

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)