I have been looking at a client’s WordPress install the last few days. They have a large number of posts and page load times in general have been increasing. I installed some query logging based on the code in this article …
Rails-Like SQL Query Logging in WordPress
Checking over the logs it became obvious that queries using SQL_CALC_FOUND_ROWS are a problem. Digging through code it appears that where a limit on results is used it uses this query modifier to allow simpler coding of paging. The problem is that since the main query to grab blog posts on the blog home page or any archive page uses this, on a large blog this becomes quite inefficient.
For now I have tweaked MySQL so that the MySQL Query Cache is now larger. These articles were useful:
MySQL Query Cache (at MySQL Performance Blog)
Turn on MySQL query cache to speed up query performance?
Looking at the log after this change query times have dropped dramatically (from over 0.5 seconds and up to milliseconds). This has immediately improved performance of the blog and overall load on the server.
I am currently looking at some WP Caching API and plugins to further improve performance.
If you are looking for some more ways to speed up WordPress check out this article as a starting point:
4+1 Ways To Speed Up WordPress With Caching
And … for myself – just so I remember:
Install Memcache onto cPanel running CentOS