Every covering index in this series so far — the sitemap last-modified fix, the batch of five index-migration commands shipped across a network — got measured on the read side: query time before, query time after, a clean before/after table. None of those posts talked about the other half of the ledger. An index isn’t free. Every INSERT and UPDATE that touches an indexed column has to update that index too, and on a WordPress VIP-style multisite install, "every write" doesn’t mean one site’s writes — it means every site sharing that MySQL tier, because they’re typically all landing on the same physical database server even though each site gets its own table prefix.
That’s the piece worth writing down explicitly: a sizing checklist for whether a covering index actually pays for itself, applied before the ALTER TABLE ships, not discovered afterward as an unexplained write-latency regression on some unrelated site sharing the same instance.
TL;DR: Every covering index in this series got measured on the read side, never the write side — but on shared multisite MySQL, every write touching an indexed column is paid by every site sharing that instance, not just the one with the slow query. Checklist before shipping: write frequency, query frequency weighed against a longer cache TTL, projected row growth, and whether the fix should be network-wide or per-site.
Why Is the Read Side the Easy Half?
Deciding an index would help a specific slow query is mechanical: run EXPLAIN ANALYZE, look for a Sort or Using filesort step whose cost scales with the matched row count instead of the query’s LIMIT, and confirm a proposed index’s column order would let MySQL satisfy both the WHERE clause and the ORDER BY without a separate sort pass. Covered in detail elsewhere in this series — that part isn’t the hard question.
The hard question is whether the read win is worth what the index costs on every write to that table, indefinitely, for every site sharing the instance.
What Three Numbers Do You Need Before Adding the Index?
1. Table write frequency, on the actual columns the index touches. A wp_posts table on a high-traffic news site can see thousands of post_modified_gmt updates a day just from editorial activity — every save, every scheduled-publish transition, every autosave that touches the row. An index on that column pays a small write-side cost on every one of those, forever. Check this with the same profiler that finds slow reads — the SAVEQUERIES-based tool from earlier in this series reports duration, but a quick --context filter for UPDATE wp_posts over a representative crawl window gives a real write-frequency number instead of a guess.
2. Query frequency on the read side, weighted by actual traffic — not worst-case. A query that runs once a day at 9 seconds is a very different sizing decision than one running every second at 200ms that occasionally spikes to 9 seconds under a specific condition. The former might be fine left slow and hit with a longer output-cache TTL instead of an index; the latter needs the fix regardless of write cost, because it’s compounding continuously.
3. Row count and growth rate, not just current size. An index that costs nothing measurable on a 5,000-row table can become a real write-latency contributor once that table crosses several hundred thousand rows — which, on a content site, is a matter of when, not if. Sizing against today’s row count on a table that grows without bound is sizing against the wrong number.
What Does "Shared Instance" Actually Multiply?
The detail that’s easy to miss on a genuinely multisite install: adding an index to a shared plugin’s table — one that every site on the network runs, with the same schema, just a different table prefix — multiplies the write cost by however many sites are active on that instance. An index that’s clearly worth it on the network’s single largest site, evaluated in isolation, might be a net negative in aggregate once every smaller site on the same shared MySQL tier is paying the identical write-side cost for a read-side win only one site actually needed.
The practical version of this: size the index against the specific site (or small set of sites) that actually has the read problem, and consider whether the fix belongs as a per-site index rather than a schema change applied network-wide through the shared plugin. The batch of index-migration commands covered earlier were run with an explicit --network flag and a status check per site precisely because "does every site need this" and "does every site already have this" are two different questions, and only checking the second one lets an unnecessary index quietly ship everywhere the network happens to touch.
What’s the Full Checklist?
- Measure write frequency on the target columns with the same profiler used to find the slow read — don’t guess.
- Weigh query frequency against a longer output-cache TTL as an alternative fix. Not every slow, rare query needs a schema change; some just need to be hit less often.
- Size against projected row count, not today’s, for anything on a growing content table.
- On a shared multisite instance, ask whether the fix needs to be network-wide or site-specific before defaulting to "ship it everywhere the plugin runs."
- Validate with
EXPLAIN ANALYZEbefore and after on the read side, and a repeated-warm-run write-latency check on a representative write path before and after on the write side. One side without the other is half a measurement.
What’s the Generalizable Lesson Here?
- An index is a trade, not a strict win: read cost down, write cost up, permanently, for every writer that touches the indexed columns.
- On shared infrastructure, "every writer" can mean every tenant on the instance, not just the one team that filed the performance ticket. Size accordingly.
- Query frequency and table growth rate change the answer over time even when nothing about the query itself changes. A sizing decision made once, on today’s numbers, is worth revisiting as the table grows.
- A longer cache TTL is a legitimate alternative to a schema change for anything slow but infrequent — don’t reach for
ALTER TABLEas the only tool. - "Does this need to run network-wide" and "has this already run on this site" are separate checks. Conflating them is how an unnecessary index ships to sites that never needed it.