Slow SQL query after update to 6.9.4

Hi Trinopoty,

I ran tests on a similar MariaDB setup and can now explain exactly what’s happening.The Query Structure Is Identical

WordPress did NOT change the query between versions. Both 6.6.1 and 6.9.4 generate the exact same SQL:

SELECT wp_posts.ID 
FROM wp_posts 
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 
WHERE wp_term_relationships.term_taxonomy_id IN (43,89,114,...)
  AND wp_posts.post_type = 'post' 
  AND wp_posts.post_status = 'publish' 
ORDER BY wp_posts.post_date DESC 
LIMIT 0, 10

What Actually Changed

The difference is MySQL’s query optimizer choosing a different join order based on your data statistics. In my testing, I reproduced both plans on the same database:

Plan A (Fast – 0.01s) – What you had in 6.6.1

-> wp_posts (type_status_date index) → 38 rows
  -> wp_term_relationships (PRIMARY key) → 10 rows

Why it’s fast: Starts with highly selective wp_posts filter (post_type=’post’, post_status=’publish’), already sorted by post_date from the index.

Plan B (Slow – 1.20s) – What you have now

-> wp_term_relationships (term_taxonomy_id index) → 196,836 rows  
  -> wp_posts (PRIMARY key lookup per row) → filter down to 10
  -> Filesort required (using temporary table)

Why it’s slow: Starts with the term relationships, fetches ~200k rows, then filters. The filesort on that large intermediate result kills performance. Why Did This Happen?

Most likely one of these changed between your versions:

1. Your data grew – More posts were added, making the optimizer think the term_taxonomy_id filter is more selective than the post_type/post_status filter
2. Statistics refreshed – Running ANALYZE TABLE or the auto-analyze triggered after the WordPress upgrade
3. Index cardinality estimates shifted – The optimizer’s cost model now incorrectly estimates that starting with term_relationships is cheaper

I verified this by using FORCE INDEX hints to make MariaDB use the “bad” plan – the same query went from 0.3ms to 1.3ms even on my small test database (105 posts, 23 relationships).

Solutions (in order of effectiveness)

1. Object Caching (Recommended – immediate fix)

Install a object cache plugin. There’s many to choose from that use memcache or redis. I like docket cache which just uses php files and is the easiest to install and setup.

2. Enable split_the_query (WordPress built-in)

add_filter( 'split_the_query', '__return_true', 20 );

This forces WordPress to run two simpler queries instead of one complex JOIN, which often avoids optimizer issues.

3. MySQL Optimizer Hints (might work)

add_filter( 'posts_clauses', function( $clauses ) {
    if ( strpos( $clauses['join'], 'term_relationships' ) !== false ) {
        // Force join order: posts first
        $clauses['join'] = 'STRAIGHT_JOIN ' . $clauses['join'];
    }
    return $clauses;
}, 10, 2 );

Long-term Fix

This is a known WordPress core issue tracked in Trac #54346 (https://core.trac.wordpress.org/ticket/54346). The proposed fix converts the LEFT JOIN to a subquery, which eliminates the join order ambiguity:

SELECT wp_posts.ID FROM wp_posts 
WHERE wp_posts.ID IN (
    SELECT object_id FROM wp_term_relationships 
    WHERE term_taxonomy_id IN (43,89,114,...)
)
AND wp_posts.post_type = 'post' 
AND wp_posts.post_status = 'publish' 
ORDER BY wp_posts.post_date DESC 
LIMIT 0, 10

I recommend:

1. Immediate: Implement object caching (Solution 1)
2. Short-term: Comment on Trac #54346 with your EXPLAIN output to help prioritize the fix
3. Long-term: Monitor that ticket for the subquery rewrite merge