In the last post, I crawled a WordPress VIP-style multisite network with a small SAVEQUERIES-based profiler, looking for slow paths a production log sample hadn’t caught. It found one: on one of the larger sites in the network — a regional news publisher with roughly 100,000 published posts — sitemap_index.xml was consistently taking nine to eleven seconds to generate. Not occasionally. Every time, warm cache included.
This post is the deep dive: what was actually wrong, why the obvious fixes didn’t apply, and how to ship a fix like this without it blowing up in your face during the deploy window.
TL;DR: A hardcoded USE INDEX hint inside a private Yoast SEO method was sorting on the wrong column, forcing MySQL to re-sort ~100,000 rows on every sitemap request. The fix was a new covering index plus a global query filter that rewrites the hint — guarded by an existence check so the deploy order of "new index" vs. "new code" can’t turn a slow query into a hard error. Isolated query time dropped from ~9.5s to ~65ms.
How Do You Diagnose It? EXPLAIN ANALYZE Doesn’t Lie
The very first move with any slow query is to stop guessing and ask MySQL directly what it’s doing:
EXPLAIN ANALYZE
SELECT DISTINCT post_modified_gmt
FROM wp_posts USE INDEX (`type_status_date`)
WHERE post_type = 'post'
AND post_status = 'publish'
ORDER BY post_modified_gmt DESC
LIMIT 1000;
(Simplified — the real query, generated by Yoast SEO’s sitemap code, uses a window function to bucket posts into sitemap pages. The shape of the problem is the same either way.)
The EXPLAIN ANALYZE output showed an index being used, a Sort step immediately after, and a cost that scaled with the entire matching row count — not the LIMIT. That combination is the signature of an index that doesn’t actually satisfy the query’s ORDER BY. MySQL was walking the index for the WHERE clause, then explicitly re-sorting close to 100,000 rows in memory (or on disk) because the index’s own order didn’t help.
The index in question, type_status_date, is ordered by post_type, post_status, post_date. The query orders by post_modified_gmt — a completely different column. The hint wasn’t wrong so much as it was answering a different question than the one being asked.
What Do You Do When the Fix Lives in a Private Method You Don’t Own?
Normally, at this point, you’d add a covering index and move on. Two things made this one more interesting.
First, this code lives inside Yoast SEO — the SEO plugin that a large fraction of serious WordPress sites run — not in first-party code. You don’t want to fork a third-party plugin to fix one query; forks rot, and every Yoast update becomes a manual merge from then on.
Second, the method responsible — WPSEO_Post_Type_Sitemap_Provider::get_all_dates_using_with_clause(), with a fallback for older MySQL called get_all_dates() — is declared private. You can’t override it by extending the class, which is exactly the trick Yoast’s own sitemap providers use elsewhere in their codebase for customization. And it builds the query as a raw SQL string via $wpdb->get_col(), not through WP_Query — so none of WordPress’s usual posts_clauses-style filters get a chance to touch it either.
At that point you’ve run out of "supported" extension points. What’s left is WordPress’s own last line of defense: the global query filter, which runs on every SQL string right before it hits the database, regardless of where that string came from.
add_filter( 'query', function ( string $sql ): string {
if ( ! is_sitemap_lastmod_sampling_query( $sql ) ) {
return $sql;
}
// ... rewrite the index hint, see below ...
return $sql;
} );
The obvious risk with a filter this broad is collateral damage — accidentally rewriting some other query that happens to share a substring. The fix is to make the match signature as specific as the thing you’re actually targeting, not just the index name:
function is_sitemap_lastmod_sampling_query( string $query ): bool {
if ( false === strpos( $query, '`type_status_date`' ) ) {
return false;
}
// Yoast's two internal code paths: a window-function variant for
// modern MySQL, and a session-variable fallback for older versions.
// Requiring one of these alongside the index name rules out anything
// else that happens to reference the same index.
return false !== strpos( $query, 'ROW_NUMBER() OVER' )
|| false !== strpos( $query, '@rownum' );
}
This is worth a unit test on its own, independent of WordPress or the database — it’s a pure string-matching function, so there’s no excuse not to pin it down with a few assertTrue/assertFalse cases and move on with confidence.
What’s the Fix? A Covering Index, Then a Surgical Replacement
The actual fix has two parts. First, add an index that genuinely covers the query — ordered by the column it’s actually sorting on:
ALTER TABLE wp_posts
ADD INDEX idx_sitemap_modified_gmt (post_type, post_status, post_modified_gmt);
Second, rewrite the query string to reference it instead of the mismatched one:
function rewrite_index_hint( string $query ): string {
if ( ! is_sitemap_lastmod_sampling_query( $query ) ) {
return $query;
}
return str_replace(
'`type_status_date`',
'`idx_sitemap_modified_gmt`',
$query
);
}
With the new index in place, MySQL no longer needs a separate sort step at all — the index already returns rows in the order the query wants them, so the plan collapses to a straightforward index scan up to the LIMIT.
Why Can Deploying This Fix Cause an Outage?
Here’s the part that’s easy to get badly wrong. USE INDEX — and its more aggressive cousin, FORCE INDEX — referencing an index name MySQL doesn’t recognize isn’t a slow query. It’s a hard error (MySQL error 1176: "Key … doesn’t exist in table").
That matters a great deal for how you sequence a deploy. The index has to be created by a separate step — a migration, a CLI command, whatever your deploy process uses — and that step is not guaranteed to run atomically with the code deploy. If the code that rewrites the query hint goes live before the index has actually been created on a given database, every request touching that query starts hard-failing instead of merely being slow. You’ve turned a performance bug into an outage.
The fix is to never trust that the index exists — check, every time, with a result cached for the duration of the request:
function target_index_exists( string $table ): bool {
static $cache = [];
if ( isset( $cache[ $table ] ) ) {
return $cache[ $table ];
}
global $wpdb;
$exists = (bool) $wpdb->get_var(
$wpdb->prepare(
"SELECT 1 FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = %s
AND INDEX_NAME = %s
LIMIT 1",
$table,
'idx_sitemap_modified_gmt'
)
);
return $cache[ $table ] = $exists;
}
function rewrite_index_hint( string $query ): string {
if ( ! is_sitemap_lastmod_sampling_query( $query ) ) {
return $query;
}
$table = extract_target_table( $query ); // parse the table name out of $query
if ( null === $table || ! target_index_exists( $table ) ) {
return $query; // fall back to the original, always-present hint
}
return str_replace( '`type_status_date`', '`idx_sitemap_modified_gmt`', $query );
}
Now the rewrite is safe to deploy in either order relative to the index creation step. Before the index exists, the query falls back to its original (slow, but correct) behavior. After, it gets the fast path. Verify both states explicitly before shipping — hit the endpoint with the index missing and confirm you get a 200 response at the old, slow speed rather than a 500; then create the index and confirm the fast path kicks in.
This generalizes well beyond this one bug: any code path that references a schema object your deploy process creates separately from your code deploy needs an existence guard, full stop.
How Much Faster Did It Get?
Measured with the repeated-warm-runs discipline from the last post — three runs each, discarding the first as warm-up:
| Before | After | |
|---|---|---|
| Isolated query | ~9.5s | ~65ms |
| Full page (real HTTP request) | ~11s | ~0.5s |
The explicit sort step disappears from the query plan entirely. Validated against a second site in the same network with a smaller dataset — the win shrinks somewhat at smaller scale (a MySQL index only pays for itself once there’s enough data to make sorting expensive in the first place), but stays a clear, unambiguous improvement in every case tested.
One measurement wrinkle worth flagging explicitly: Yoast SEO caches its own generated sitemap XML output. A second HTTP request to the same endpoint will come back fast regardless of whether the underlying query got fixed, because you’re hitting the output cache, not the database. That’s not a cache-warmth artifact in the sense of the last post — it’s a genuinely different caching layer sitting in front of the thing you’re trying to measure. The reliable signal here was EXPLAIN ANALYZE against the database directly; the HTTP-level check was only useful as a secondary "does this still return valid output" sanity check, never as the primary evidence.
What’s the Generalizable Lesson Here?
Strip the WordPress and Yoast specifics away, and what’s left is a pattern that shows up constantly in any sufficiently large, sufficiently old application built on top of third-party code:
- A missing or mismatched index rarely announces itself as "missing" — it shows up as a plan that scales with row count instead of
LIMIT, visible only if you actually readEXPLAIN ANALYZEinstead of trusting a raw timing number. - When the code you need to fix lives in a private method of a dependency, the least invasive point of leverage is often not the language’s normal extension mechanism (subclassing, hooks scoped to that layer) but the lowest layer you have legitimate access to intercept — here, the database driver’s own query filter.
- Any interception that broad needs a narrow, specific match signature, or you will eventually touch a query you didn’t mean to.
- Index hints and the schema objects they reference are two separate deploy artifacts unless you go out of your way to make them atomic. Guard accordingly.
- Trust the database over the wire. Output caches, CDNs, and object caches all sit between "the query is fast" and "the response was fast," and only one of those is the thing you actually fixed.