PATTERN Cited by 1 source

Bidirectional index-query attribution¶

Problem. Operators ask two equally-important questions about index usage:

"Which indexes does this query use?" — the per-query view. Useful when debugging a slow query or verifying that a newly-added index is picked up.
"Which queries use this index?" — the per-index view. Useful when deciding whether it's safe to drop an index, understanding blast radius before a schema change, or hunting down consumers of an index that's becoming a write-amplification hotspot.

Most observability surfaces answer one direction well and the other poorly (EXPLAIN → direction 1 only; performance_schema.table_io_waits_summary_by_index_usage → a muddied direction 2 without query attribution).

Solution. Build both directions as first-class search predicates over the same per-query-used-index-set primitive. Expose predicates like index:table_name.index_name for direction 2 and an implicit per-pattern index-usage view for direction 1. Compose with other predicates (query-count, latency, tags) to filter the result set by operational relevance.

Canonicalised by PlanetScale Insights (Rafer Hazen, 2024-08-14). (Source: sources/2026-04-21-planetscale-tracking-index-usage-with-insights.)

Direction 1 — per-query view¶

Surface: click a query pattern in the Insights dashboard, open the Indexes tab (see concepts/index-usage-time-series). You see:

A time-series graph of each index's hit percentage for that query pattern over time.
A cumulative bar graph aggregating usage over the selected time span.

Canonical framing verbatim: "The main graph shows a time series of the percentage of queries that use each index."

Direction 2 — per-index view¶

Surface: on the main Insights query-list page, add index:table_name.index_name as a search term. The query list filters to patterns that touched the named index in the current time window.

Canonical framing verbatim: "to answer this, go to the main Insights page, and add index:$TABLE_NAME.$INDEX_NAME as a search term."

Example: index:products.idx_price returns every pattern that used the idx_price index on the products table during the active time range.

Direction-2.5 — unindexed view¶

Surface: add indexed:false as a search term. The query list filters to patterns that used no index at all — i.e. the empty per-query used-index set.

Canonical framing verbatim: "We can also use insights to find a list of query patterns that use no indexes at all by entering indexed:false in the insights search box."

Composition with other predicates¶

The killer feature is composability. The post's own example combines three predicates:

indexed:false query_count:>1000 p50:>250

Translation: unindexed query patterns, executed more than 1000 times in the last 24 hours, with p50 latency > 250 ms. This single search surfaces the specific subset of patterns where adding an index would matter most — high-volume + slow + currently unindexed. The triple filter replaces a multi-step workflow on MySQL-native substrates:

Query events_statements_summary_by_digest, sort by runtime share.
Cross-reference table_io_waits_summary_by_index_usage for the null-index hit-rate on each candidate.
Filter to patterns with latency above threshold.

Insights collapses these three steps to one predicate expression (see also patterns/digest-based-query-prioritization).

Sibling predicates¶

The same predicate grammar hosts:

tag:key:value / tag:key — query tag filters (concepts/query-tag-filter).
actor:<username> — authenticated-principal filter (from the 2022-09-27 error-debugging ingest; concepts/actor-tagged-error).
query_count:>N, p50:>ms, p95:>ms — aggregate statistics filters.
indexed:false, index:table.idx — the new predicates canonicalised by this pattern.

Composability across all of these is the structural reason Insights' observability surface is stronger than the sum of its individual dashboards.

Drop-index workflow¶

Direction 2 (index:table.idx) is the canonical read for drop-index decisions:

index:products.idx_stale with a 24-hour or 7-day window — are there any patterns using it?
If empty — candidate for drop. But pair with the SELECT-only caveat two-step verification before executing.

Caveats¶

SELECT-only — see concepts/select-only-index-telemetry-caveat. Both directions of attribution only cover SELECT paths; an index may have zero Insights hits in direction 2 and still be load-bearing for UPDATE / DELETE.
Time window sensitivity — "no patterns used this index in the last 24h" can mean "it's used daily" plus "bad sample window." Extend the window before concluding.
Pattern granularity — a pattern can aggregate thousands of raw queries; if the direction-2 query returns a few patterns with high volumes, the downstream blast-radius is amplified by the per-pattern execution count.
Bar-graph vs time-series interpretation — the cumulative bar graph can mask transient optimiser flips. Use the time-series view to detect plan-flapping even when the cumulative bar suggests one index dominates.

Seen in¶

sources/2026-04-21-planetscale-tracking-index-usage-with-insights — Rafer Hazen (2024-08-14) canonicalises both directions of attribution in a single feature launch. Direction 1 (per-query → indexes) via the Indexes tab; direction 2 (per-index → queries) via the index:table.index search predicate; the empty-set special case via the indexed:false predicate. Composes with existing predicate grammar (query_count, p50, tag:) — the combined indexed:false query_count:>1000 p50:>250 filter is the canonical example of the pattern's power. Built on the concepts/per-query-used-index-set primitive captured via the handler-hook sidecar telemetry pattern.