A client recently reached out in regard to a reproducible performance problem on their WordPress site.  The use case and conditions were as follows:

• Performance was fine until a change happened to the wp_posts or wp_term_relationships tables (i.e. when a new blog post is created, or an existing post is modified)
• Once those tables changed, related result sets in the query cache were cleared, rebuilt, and re-cached
• During re-caching, the site was brought to it’s knees due to (otherwise performant) queries executing against an empty cache.  Once re-caching completed, those queries returned their result sets much faster, and everything returned to normal.

Fortunately, there had already been some investigation by the time I began troubleshooting.  The client had their mysql long_query_time set to 10 seconds, and the slow query log was reporting tens of variants of this query during problematic conditions:

SELECT wp_posts.* FROM wp_posts
INNER JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
WHERE 1=1
AND wp_term_relationships.term_taxonomy_id IN (32)
AND wp_posts.post_type = 'post'
AND wp_posts.post_status = 'publish'
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 12;

Unfortunately, that query’s execution plan cites Using temporary and Using filesort operations, which are both very expensive (the query engine must create temp table(s), sort the result, etc.).  The fact that the query was taking 1.5 seconds to return 12 records (on a cold query cache) supplements that claim.

Furthermore, something I questioned even before the execution plan was why the query made use of a group by clause on the table’s primary key w/out the use of any aggregate functions.  The join conditions and where clause used in the query assured a 1:1 join result, so using distinct, whether explicitly or implicitly via group by, wasn’t needed.  After removing it, I confirmed Using temporary and Using filesort were no longer in the execution plan:

As a result, we now have a query that performs 1,500 times faster.  The original query completed in about 1.5 seconds, whereas the revised query completes in less than a millisecond!  Even better news … after the optimization, the query is no longer reported in the MySQL slow query log.

Update: even though the optimization worked well here, it only worked part of the time in the production environment.  But on a positive note, an upgrade to MySQL 5.5 rendered the optimization obsolete.  Apparently the well known performance improvements in MySQL 5.5 were enough to fix this problem with no other effort required. Hooray for a MySQL 5.5 update!