Mysql – Optimize WordPress Mysql Query that take 5 seconds to execute

MySQLmysql-5.6

Here is my original post: https://wordpress.stackexchange.com/questions/189736/optimize-wordpress-query-that-take-5-seconds-to-execute

I have a large wordpress mysql database (Server version: 5.6.22):

rows in key tables:

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

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

 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 by wp_posts.ID DESC but get the same time to execute the query.

Just to add more information here on dba.stackexchange – it's the following conditions that cause the most time in this query: 

GROUP BY wp_posts.ID
ORDER BY  wp_posts.ID DESC

When I remove the group by and order by conditions and use a SELECT distinct(wp_posts.ID) it comes back in 0.0 seconds but with obviously the wrong results sorting (oldest to newest)….

Is there a way to tell mysql to do the scan in reverse by grabbing the newest to oldest using SELECT distinct(wp_posts.ID) without using order by?

Best Answer

Here is the modified query

select 
p.ID from wp_posts p
inner join wp_term_relationships wtr on p.ID = wtr.object_id
left join wp_postmeta wpm on p.ID = wpm.post_id and wpm.meta_key = '_Original Post ID'
where 1=1
and p.ID != 731467
and wtr.term_taxonomy_id = 5
and p.post_type = 'post'
and ( p.post_status = 'publish' or p.post_status = 'private')
and ( wpm.post_id IS NULL or ( wpm.meta_key = '_Original Post ID' and cast(wpm.meta_value AS CHAR) = 'deleted') )
group by p.ID
order by p.ID desc
limit 0,20

In the original query you have a left join to wp_postmeta again and this is not needed since you are using the filtering at the end which could refere to the joined table which is already done.

And also need to apply the index on the joining key on wp_term_relationships 

alter table wp_term_relationships add index object_id_idx(object_id);

Now Or clause are usually killer and its better to convert them to union which are better than or

(
 select 
 p.ID from wp_posts p
 inner join wp_term_relationships wtr on p.ID = wtr.object_id
 left join wp_postmeta wpm on p.ID = wpm.post_id and wpm.meta_key = '_Original Post ID'
 where 1=1
 and p.ID != 731467
 and wtr.term_taxonomy_id = 5
 and p.post_type = 'post'
 and p.post_status = 'publish' 
 and wpm.post_id IS NULL 
)
union
(
 select 
 p.ID from wp_posts p
 inner join wp_term_relationships wtr on p.ID = wtr.object_id
 left join wp_postmeta wpm on p.ID = wpm.post_id and wpm.meta_key = '_Original Post ID'
 where 1=1
 and p.ID != 731467
 and wtr.term_taxonomy_id = 5
 and p.post_type = 'post'
 and p.post_status = 'private'
 and wpm.meta_key = '_Original Post ID' 
 and cast(wpm.meta_value AS CHAR) = 'deleted'
)
group by p.ID
order by p.ID desc
limit 0,20
Related Question