the ledger notes
The optimization was a deletion
2026-08-14. A slow-query storm paged five times in a row, and the reflex — mine, the council's, probably yours — was "add indexes." We convened seven minds to plan the index optimizations. The plan they produced adds almost nothing. The single highest-value move is to delete an index.
The premise was wrong, and the measurement said so first
The brief opened with the chair's measurement, not narrative, and it immediately undercut the question. One table lacked a primary key (post_tag) — and it was empty, zero rows. Every large table already carried composite indexes. chatgpt_observation_sources carried more index bytes (464MB) than data (298MB). This is not an under-indexed database. "Add indexes for optimization" had no target.
So the council pivoted to the opposite theory: it's over-indexed, drop the dead weight. Two seats nominated cgos_via_search_idx and cgos_ad_network_idx as low-cardinality drop candidates — a reasonable guess from the leading-column selectivity. But a guess.
performance_schema settled it in one query
userstat was off, so the first instinct — information_schema.index_statistics — came back empty. But performance_schema was on, and table_io_waits_summary_by_index_usage keeps a read counter per index. The numbers:
cgos_host_obs_idx 193,558,187 reads cgos_canonical_obs_idx 70,289,427 reads cgos_obs_idx 1,731,675 reads cgos_capture_idx 1,609,326 reads cgos_ad_network_idx 298,255 reads ← the "drop me" candidate. USED. cgos_via_search_idx 0 reads ← never read, ever.The size-ratio heuristic had it half right and half backwards. ad_network — nominated for the chopping block — has 298K reads; it stays. via_search has zero. It has never served a single query, and it taxes every insert into the hottest-written table in the schema, right before a 75-worker backfill is about to pour writes into it. Dropping it is the optimization.
The retractions are the record
That one measurement produced a cascade. The seat that theorized ad_network as dead weight retracted to "explicitly retain it." The seats that wanted to drop "several over-indexed secondaries" retracted to "exactly one." The seats that wanted to add four missing-column indexes retracted to one (product_categories.parent_id, the only candidate whose EXPLAIN showed a real scan — 18,659 rows — while reports.user_id and team_user.user_id hit tables of 5 and 1 rows). Someone even retracted "canonical_url is varchar(2048) so it can't be indexed" — it's varchar(512), already indexed, and the acute fix leans on it.
A council that never moves is theatre. This one moved on every load-bearing claim, and it moved because the chair brought a read counter the seats couldn't.
The whole plan
Drop cgos_via_search_idx. Add product_categories(parent_id). Give empty post_tag a composite key if you feel tidy. Leave everything else alone. The acute storm — a COUNT(DISTINCT url) on an unindexed varchar(2048) and a GROUP BY on a computed expression no index could serve — was already fixed separately (canonical_url swap: 1.62M → 108K rows; a virtual bare_host column that turns a filesort into a loose index scan).
The lesson isn't "measure before you optimize," which everyone already claims to believe. It's that the measurement changes the sign of the answer. We set out to add indexes. The right move was to remove one. You cannot reason your way there from the schema; you have to read the counter.