CONCEPT Cited by 1 source

`SELECT`-only index-telemetry caveat¶

The invariant: PlanetScale Insights' per-query-pattern index-usage telemetry — surfaced via the Indexes tab, the index:table.index search predicate, and the indexed:false filter — only covers SELECT queries. UPDATE and DELETE executions are not reported into Insights' index-usage surface.

Canonical disclosure verbatim: "index information is only reported for SELECT queries, so it's important to independently verify that indexes aren't being used in UPDATE or DELETE queries before removing an index." (Source: sources/2026-04-21-planetscale-tracking-index-usage-with-insights.)

Why this matters: the drop-index trap¶

The obvious workflow on seeing an index that shows zero use in Insights' Indexes tab is "drop it." That workflow is unsafe in the presence of this caveat.

A production index can be load-bearing for UPDATE / DELETE paths via two mechanisms:

Predicate evaluation — UPDATE t SET x=1 WHERE user_id=? walks the index on user_id to locate the target rows. Insights does not report this index hit.
Row lookup during constraint enforcement — cascading foreign-key referential checks, unique-index uniqueness validation, trigger-driven lookups all use indexes that Insights does not report.

Dropping such an index based on Insights-only signal converts the DML path from indexed lookup (single-row B+tree walk) to full-table scan (N-row walk). On a large table this can degrade UPDATE latency from milliseconds to minutes, and under write-heavy load can cascade into lock contention and connection-pool exhaustion.

Workflow: the two-step verification¶

Insights Indexes tab shows 0% → candidate for removal.
Independently verify DML paths before removal. Three methods, not mutually exclusive:
EXPLAIN each representative DML query. For every UPDATE / DELETE statement the codebase issues against this table, run EXPLAIN (on a pre-prod replica) and inspect key, key_len, and possible_keys.
Query the server-local performance_schema.table_io_waits_summary_by_index_usage directly — this counter does capture DML row reads per index. Low numbers there corroborate low Insights numbers and are the safer read for drop decisions.
Audit ORM / codebase query log. Grep for every path that writes to the table; check each against the candidate index.

Dropping the index with confidence requires all three signals (Insights + per-(table,index) counters + code audit) agreeing on "no usage."

Why the scope gap exists¶

The post doesn't explicitly justify the scope decision. Plausible reasons in descending likelihood:

Engineering cost — SELECT is the dominant path by volume; instrumenting DML paths would require further handler-layer changes with more risk of performance regression on the write path.
Response-packet semantics — the wire-protocol sidecar rides in the result-set response packet. UPDATE / DELETE responses carry OK_Packet/affected_rows, not result-set framing; threading a per-query-index-set field through requires additional changes.
User expectation — most operators drop "unused" indexes off the back of slow-query log + EXPLAIN for SELECT; DML coverage is a nice-to-have rather than the 80th-percentile workflow.

Operational risk scaling¶

The drop-index trap's blast radius scales with:

Table size — unindexed UPDATE on a 10-row table is cheap; on a 100 M-row table it's a minutes-long scan.
Write QPS — a high-QPS write path that loses its supporting index saturates locks + connection pool.
Foreign-key and cascading-constraint count — every FK reference into the table amplifies the cost of dropping the wrong referential index.

Sibling caveat¶

Symmetrically: a performance_schema-only drop decision is also unsafe — per-(table, index) counters reset on server restart and are global across all query patterns, so "counter is zero" may mean "server restarted recently" rather than "no queries use this index." Insights complements performance_schema on the SELECT side; performance_schema complements Insights on the DML side; both together with EXPLAIN give the safe drop signal.

Seen in¶

sources/2026-04-21-planetscale-tracking-index-usage-with-insights — Rafer Hazen (2024-08-14) flags the SELECT-only scope as a note near the close of the post. The advisory: "independently verify that indexes aren't being used in UPDATE or DELETE queries before removing an index." The post does not operationalise the verification workflow — it flags the gap and moves on. This concept page canonicalises the two-step verification as the standard operator response to the caveat.

SELECT-only index-telemetry caveat¶