Skip to content

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

  1. Identify candidate tables via patterns/digest-based-query-prioritization — queries touching a common table with high rows_examined_avg.
  2. Query table_io_waits_summary_by_index_usage for each candidate table.
  3. For tables with high null-index reads: use EXPLAIN on the digest's typical query to confirm the plan is type: ALL (full scan).
  4. 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

Last updated · 378 distilled / 1,213 read