The last deep dive in this series covered a mismatched USE INDEX hint buried in a private method of Yoast SEO’s sitemap code — found by systematically triaging a production slow-query log, where sitemap-related queries kept showing up as a disproportionately expensive category on a WordPress VIP-style multisite network of regional news sites. That triage turned up two more bugs in the same subsystem, neither of which looked anything like the first one. This post covers both, plus the general technique behind fixing all three without maintaining a plugin fork.
TL;DR: Two more bugs in the same third-party sitemap code: an N+1 pattern computing per-term timestamps (197 queries collapsed into 1), and a hardcoded 100-row pagination batch size that made OFFSET queries progressively more expensive (~10x reduction after fixing). Both fixed by swapping the plugin’s internal provider objects at runtime — no fork required.
What Was the First Bug? An N+1 Hiding in Plain Sight
The first one was the easy kind to spot, once you’re looking at the right query log line: the same query shape, repeating once per row of something. Yoast’s stock taxonomy-sitemap provider computes each term’s "last modified" timestamp with a separate query, once per term. A taxonomy with 197 terms means 197 nearly-identical queries to render a single sitemap page — a textbook N+1 pattern, just one layer removed from the usual "N+1 in a template loop" story, since this one lives inside a plugin’s internal sitemap-building code rather than anything you’d normally profile a template for.
The fix collapses all of it into one query:
SELECT term_id, MAX(post_modified_gmt) AS last_modified
FROM wp_posts
INNER JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
INNER JOIN wp_term_taxonomy ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
WHERE wp_term_taxonomy.taxonomy = 'category'
AND wp_posts.post_status = 'publish'
GROUP BY term_id;
One GROUP BY query computes the max modified timestamp for every term in a single pass; the sitemap builder then reads each term’s value out of an in-memory array instead of issuing a fresh query per term. 197 queries become 1.
What Was the Second Bug? A Pagination Batch Size Nobody Checked
The second bug is the more interesting one, because it doesn’t look like a bug at all until you go read the plugin’s own pagination logic. Yoast’s stock post-type sitemap provider paginates its internal query in fixed batches — the plugin caps each internal batch at 100 rows by default, regardless of how many entries a given sitemap page actually needs to contain.
That’s fine if a sitemap page needs 100 rows or fewer. It’s expensive if it needs more: building one page covering, say, 1,000 posts meant issuing ten separate 100-row queries, each using OFFSET to skip ahead to the next chunk. OFFSET-based pagination gets progressively more expensive as the offset grows, because the database still has to scan and discard every row it’s skipping past before it can start returning the ones you actually want — the tenth 100-row batch, at OFFSET 900, does meaningfully more work than the first one at OFFSET 0.
The fix is almost boring in isolation: set the provider’s internal batch step size to match the actual page size the sitemap needs, instead of leaving it hardcoded at a default meant for a different use case. Ten escalating-cost OFFSET queries collapse into one. Measured on the same taxonomy from bug one: roughly 920,000 total rows scanned to build one sitemap page, down to about 93,000 — a ~10x reduction, without touching the query’s WHERE clause at all.
Worth calling out explicitly: this bug was sitting right next to the first one, in the same subsystem, and looked nothing like it. One was "the same query, too many times." The other was "one query, doing more work per call than it needed to." Fixing the first doesn’t make you notice the second — you have to actually go read how the surrounding code paginates, not just count query repetitions in a log.
How Do You Fix a Plugin’s Internals Without Forking It?
Both fixes — plus the index-hint rewrite from the previous post — share a constraint: none of this is application code you own. It’s inside a third-party plugin, and forking a widely-used SEO plugin to carry a permanent local patch is a maintenance tax you pay forever, on every future update, for the rest of the project’s life.
The alternative WordPress actually supports reasonably well: most well-built plugins register their internal objects — providers, handlers, strategies — through a controller you can reach from the outside, even if the individual methods on those objects are private or protected. If you can get a reference to the controller, you can often just replace the object wholesale with your own subclass, rather than patching the original.
Concretely, that looks like hooking one priority tick after the plugin registers its own defaults, then mutating a public property on its controller object directly:
add_action( 'after_setup_theme', function () {
global $wpseo_sitemaps;
if ( ! isset( $wpseo_sitemaps->providers ) || ! is_array( $wpseo_sitemaps->providers ) ) {
return;
}
foreach ( $wpseo_sitemaps->providers as $index => $provider ) {
if ( get_class( $provider ) === 'WPSEO_Taxonomy_Sitemap_Provider' ) {
$wpseo_sitemaps->providers[ $index ] = new Batch_Taxonomy_Sitemap_Provider();
}
}
}, 11 );
Priority 11 matters — the plugin registers its own default providers at after_setup_theme priority 10, so this has to run one tick later, after the defaults exist, or there’s nothing yet to replace.
Two details make this reliable rather than fragile:
Matching strategy depends on what you’re guarding against. For the taxonomy provider above, an exact get_class() === 'OriginalClassName' string check is deliberate: if this file ever loads twice in the same request (a real possibility in some multisite/must-use plugin setups), a looser instanceof check would happily match your own subclass on the second pass and wrap it again. The exact string check only ever matches the plugin’s original, unmodified class. For a different provider elsewhere in the same fix, that risk didn’t apply, so a plain instanceof check was simpler and sufficient there — there’s no one-size-fits-all answer, it depends on whether double-loading is actually possible in your setup.
private doesn’t inherit. If the parent class the plugin ships declares a private property your subclass also needs internally, you cannot reuse it — PHP’s private visibility is not accessible from a subclass, full stop, regardless of what you might expect from other languages’ access modifiers. The subclass needs its own, differently-named property instead. Small, easy to lose an hour to if you don’t already know to expect it.
What Were the Results (and a False Alarm Worth Mentioning)?
Combined with a supporting five-column covering index shipped alongside both fixes, one of the network’s larger sitemap pages went from 33–46 seconds down to roughly 2 seconds warm (about 9 seconds cold). The taxonomy sitemap page from bug one went from 13.1 seconds to 0.7 seconds.
One more thing worth including, because it’s a realistic and useful coda rather than a triumphant ending: a later, separate slow-query sample — pulled some time after this shipped — still showed 6–8 seconds on one of these same sitemap pages. The instinct, understandably, was to assume a regression. Direct inspection of the database schema and the deployed code confirmed both the index and the fix were still correctly in place; the slow sample was cold-cache and contention noise from whatever was running at the moment the log was captured, not a real gap reopening. Nothing needed to be re-fixed. Worth remembering the next time a slow-log entry reappears for something you already fixed: verify what’s actually deployed before assuming the fix regressed.
What’s the Generalizable Lesson Here?
- N+1 patterns aren’t limited to your own template code — they hide just as easily inside a dependency’s internal query-building logic, and they’re just as fixable if you can reach the right extension point.
- A batch/pagination size that’s merely a default, not something intrinsic to the query, is worth checking any time you’re chasing performance in code that paginates internally. "Why is this issuing ten queries instead of one" is a question worth asking even when nothing looks obviously wrong.
- Runtime object substitution — replacing a plugin’s own internal instances with your subclasses, right after it registers its defaults — is a real, durable alternative to forking, as long as the plugin exposes the objects you need to reach, even indirectly.
- Match your replacement logic as narrowly as your actual risk requires: an exact class check where double-loading is possible, a looser
instanceofcheck where it isn’t. - Before assuming a reopened slow-query sample means a real regression, verify what’s actually deployed. Sampling noise looks identical to a real problem until you check.