6

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.

  • 3
    You might also consider Database Administrators for such a database related question. – birgire May 28 '15 at 19:26
  • Have you tried cleaning up the database (deleting unused revisions for example) and/or optimizing the tables via phpMyAdmin or something similar? Maybe that can help, too. – flomei May 28 '15 at 20:52
  • Hi flomei - Yes I deleted all revisions as I understand that can be an issue with wordpress and large data sets or just in general... – Ranknoodle May 28 '15 at 20:59
  • 1
    Is GROUP BY wp_posts.ID necessary? – KalenGi May 28 '15 at 21:20
  • Yes or use a distinct(wp_posts.ID) ...otherwise the results are multiple instances of the same wp_posts.ID – Ranknoodle May 28 '15 at 22:01
7

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)

  • 1
    @Ranknoodle with this in mind, you can intercept the DB query using the posts_request filter and replace CAST(mt1.meta_value AS CHAR) = 'deleted' with mt1.meta_value = 'deleted'. – KalenGi May 29 '15 at 11:30
  • Hi @MichaelICE - thanks for answering! Based on your suggestion, I used "AND mt1.meta_value = 'deleted'" instead of the cast value and it took the same amount of time to execute. – Ranknoodle May 29 '15 at 23:31
  • 1
    The issue is in this part of the query explain "Using temporary; Using filesort " - those seem to be the bottle neck for this query. The Order by & Group By are responsible for this behaviour and I think I may of figured out a way to work around that while getting the same result... will share once done. – Ranknoodle May 29 '15 at 23:33
  • The file sort shouldn't take that long, ours still does the file sort and its now down < 1 second. Let me know how you did. – MichaelICE Jun 10 '15 at 19:29
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.

  • 1
    Why do you think modifying WordPress core table is a good idea? – Krzysiek Dróżdż Jun 12 '15 at 4:21
  • I really don't think it's a good idea but a necessary one if running wordpress with the amount of posts and combined with the limitations of mysql not having a descending index function. Those file sorts caused by the order by operations are a deal breaker for us in regards to site performance. – Ranknoodle Jun 15 '15 at 2:34
  • But these operations are slow since you're doing it wrong. In some projects we had similar issue, but came to very different solution, that didn't modify core tables. We've created our own table and used it as indexing/search table. So every slow query was searching only based on this one table (no joins needed). (And we had much more data, AFAIR) – Krzysiek Dróżdż Jun 15 '15 at 5:06
  • Hi KRZYSIEK can you explain a little more on indexing search tables that you created? For example the slow query outline in the original question, I would create a table to store the post ID,reverse_post_id,post_type etc and only query against that? – Ranknoodle Jun 15 '15 at 16:31
  • Send me an e-mail, I'll try to elaborate on that method. – Krzysiek Dróżdż Jun 15 '15 at 16:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.