WordPress Performance Issues in core code.

  • Sumo

Hi there, while optimizing performance of one of our websites I came across this query. Query looks for recently published post and, WordPress runs this query very frequently. So on a big database it causes huge problem. Below I’ll show why.

SELECT post_date_gmt
FROM wp_posts
WHERE post_status = 'publish'
AND post_type IN ('post', 'page', 'attachment')
ORDER BY post_date_gmt DESC

This query works very slow on a database where I have 500K records in wp_posts table and it weights around 3GB.

On wp_posts table I have compound secondary index “post_status—post_type—post_date_gmt“.

And the reason it works slow is that it uses index and filesort. All of us know how expensive operation is filesort for mysql.

I looked through the code and found that function hardcoded in the source of WP, here it is:


But if you look carefully, you can see that this query is equivalent of just asking for maximum on the post_date_gmt.

So we can rewrite that function like this.

SELECT max(post_date_gmt)
FROM wp_posts
WHERE post_status = 'publish'
AND post_type IN ('post', 'page', 'attachment')

So this will return the same thing but without expensive ORDER BY and if try to EXPLAIN this query you will see that it uses only the index that I described above, and it avoids filesort. So the result could be calculated very fast!

But the thing is I can’t change WP core and I don’t know whom to ask for fixing this.

If you have any ideas on how can we propose this change, please write. Thank you!

About the Author

Edgar Marukyan is an expert of website development and performance optimization, who maintains and consults clients with high load traffic websites.. If you need an assistance, feel free to drop me a line.         Website - Twitter - Facebook

Leave a Reply

If you want a picture to show with your comment, go get a Gravatar.