PATTERN Cited by 1 source

Index-usage per-table diagnostic¶

Problem. The digest view tells you which queries are slow. It doesn't tell you which tables are being read inefficiently at the storage layer — a common diagnostic signal that some table is missing an index is when a huge volume of row reads on it are bypassing every index.

Solution. Query performance_schema.table_io_waits_summary_by_index_usage filtered to the table in question. The row with INDEX_NAME IS NULL counts reads that touched the table but used no index; the ratio of that count to the per-index counts is the diagnostic signal.

(Source: sources/2026-04-21-planetscale-identifying-and-profiling-problematic-mysql-queries.)

Canonical query¶

SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR
  FROM performance_schema.table_io_waits_summary_by_index_usage
  WHERE object_schema = 'game' AND object_name = 'message';

Worked output from the PlanetScale post:

+---------------+-------------+------------+------------+
| OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | COUNT_STAR |
+---------------+-------------+------------+------------+
| game          | message     | PRIMARY    | 0          |
| game          | message     | to_id      | 0          |
| game          | message     | from_id    | 164500     |
| game          | message     | <null>     | 2574002473 |
+---------------+-------------+------------+------------+

Diagnostic read: 2.57 billion row reads bypassed every index; only 164,500 used the from_id index; PRIMARY and to_id have zero hits. The ratio (unindexed / indexed ≈ 15,000×) tells you the workload is dominated by full-table scans of message. Either add an index the workload can use, or rewrite the queries to use an existing index.

Signals¶

`INDEX_NAME IS NULL` vs per-index	Interpretation
null >> indexed	Missing index or queries not using existing indexes.
null ≈ 0, some indexes zero	Unused indexes — candidates for removal (they cost writes forever).
null ≈ 0, all indexes active	Healthy workload.
null > 0 on a high-QPS table	Red flag regardless of ratio; at scale even a small fraction of unindexed reads dominates IO.

Workflow¶

Identify candidate tables via patterns/digest-based-query-prioritization — queries touching a common table with high rows_examined_avg.
Query table_io_waits_summary_by_index_usage for each candidate table.
For tables with high null-index reads: use EXPLAIN on the digest's typical query to confirm the plan is type: ALL (full scan).
Add an index, rewrite the query, or both.

Caveats¶

Aggregated since reset — counters accumulate from server start (or last TRUNCATE). A recently-added index won't show null-index hits from before it was added.
Per-table scope — the diagnostic doesn't surface cross-table issues (bad join orders) directly; pair with EXPLAIN ANALYZE.
Unused-index identification requires workload coverage — an index with zero hits might serve a rare-but-critical query. Don't drop without confirming via digest coverage.
InnoDB clustered-index reads via PRIMARY count as PRIMARY — a WHERE id = ? query registers on PRIMARY, not on <null>, because InnoDB's primary key is the table.
<null> is not always "no index" — it's "no index was used to locate rows" which includes full scans, sequential reads during replication, and some internal operations. Cross-check with query-digest findings.

Seen in¶

— Rafer Hazen (2024-08-14) canonicalises the productised-UX over this diagnostic in PlanetScale Insights. The indexed:false search predicate answers the same "which workload is hitting no index?" question this pattern answers at the performance_schema altitude, but at per-query-pattern granularity rather than per-table granularity. The Indexes tab adds the time-series dimension (concepts/index-usage-time-series) that pure-performance_schema diagnosis cannot provide (since counters reset on MySQL restart). Canonical framing of the three-axis completion: EXPLAIN = query-specific, not production-sampled; table_io_waits_summary_by_index_usage = production- sampled, not query-attributed, not time-series; Insights = all three. The Insights substrate relies on the per-query used-index set captured via the InnoDB index_init hook — see patterns/handler-hook-sidecar-telemetry. Operational composition: when Insights flags an indexed:false pattern, the per-table diagnostic canonicalised here is the corroborating performance_schema cross-check that surfaces the affected table; and when SELECT-only coverage limits Insights' visibility on DML paths, this pattern is the substrate that captures UPDATE / DELETE index usage for safety checks before dropping an "unused" index.
PlanetScale's field manual for query diagnosis (2024-03-29). Dicken uses this as the per-table complement to digest-based query prioritization. The worked example (2.57 × 10⁹ unindexed vs 164,500 indexed on game.message) is the canonical "this table needs an index" signal on the wiki. (Source: sources/2026-04-21-planetscale-identifying-and-profiling-problematic-mysql-queries.)