Skip to content

CONCEPT Cited by 1 source

InnoDB index_init hook

MySQL's storage-engine abstraction layer — the handler API — lets each storage engine plug into a fixed set of callbacks the server invokes during query execution. One of those callbacks is index_init(), which MySQL calls once per (query, index) pair before any row reads on that index. InnoDB's implementation of the handler exposes this hook as the intercept point PlanetScale uses to record per-query index usage.

Canonical disclosure by PlanetScale's Rafer Hazen: "The InnoDB storage handler includes an index-initialization function that MySQL calls (once) prior to using an index in a query. By recording the index name passed to this function in a per query data structure, we're able to find the set of all indexes used by each query." (Source: sources/2026-04-21-planetscale-tracking-index-usage-with-insights.)

Why the handler layer

MySQL's query execution flows through a layered stack:

SQL parser → optimiser → executor → handler API → storage engine

The handler API is the narrow waist between the engine-agnostic executor and the engine-specific storage implementation. index_init() is a handler-API callback: the executor calls it to say "about to use index N on table T", and the engine implementation (InnoDB) prepares the B+tree traversal state. Intercepting at this layer catches every actual index use — planner-optimised, adaptive-hash-index fallbacks, derived-table intermediate scans — regardless of whether the plan hint matches the runtime plan.

Contrast with performance_schema counters

MySQL ships a built-in performance_schema table, table_io_waits_summary_by_index_usage, with per-(table, index) counters that increment on every row read. The counters are:

  • Global per-server-instance — reset on restart.
  • Per-(table, index) — not per-query.
  • Always on (by default) — with measurable overhead costing most heavy workloads in practice.

The index_init hook is a different primitive: it captures per-query per-index set membership (binary: did this query use this index or not), per execution, with zero overhead on row reads (the hook fires once per query, not once per row).

Mechanics

  • Invocation: once per (query, index) pair prior to row reads starting on that index.
  • Signature (MySQL 8 ballpark): int ha_innobase::index_init(uint keynr, bool sorted) — keynr is the index ordinal on the table; the index name is looked up via the table-definition cache.
  • Capture: PlanetScale's patched fork appends the resolved index name to a per-query data structure hanging off the THD (per-connection/query context).
  • Emission: at query completion the per-query structure is serialised into the final MySQL wire-protocol packet returned to the client. See patterns/handler-hook-sidecar-telemetry for the general shape.

Scope

  • InnoDB-only — canonical verbatim: "Since PlanetScale databases exclusively use the InnoDB storage engine, we were able to focus our efforts there." MyISAM / MEMORY / ARCHIVE / CSV / federated tables would require separate handler patches.
  • SELECT-only in Insights — the hook itself fires for DML paths too (InnoDB's UPDATE / DELETE predicate evaluation may open indexes), but Insights as shipped only reports the sidecar for SELECT queries. See concepts/select-only-index-telemetry-caveat.

Caveats

  • Fork-patch, not upstream — the post implies the hook-patch lives in PlanetScale's MySQL fork. No upstream MySQL / MariaDB PR is referenced; this is proprietary telemetry machinery.
  • Handler-API stability — index_init is part of a historically-stable internal API, but not strictly public ABI. Upstream MySQL version bumps can break the patch; the post doesn't disclose the version support window.
  • Index-name resolution — the hook provides keynr (index ordinal); the patch must resolve to human-readable index names via the table-definition cache. This is typically safe but can race with concurrent ALTER TABLE that renames an index.
  • Set membership, not row counts — the hook fires once per index-open, not once per row. A query that touches an index once and reads 10 M rows from it contributes the same {index_name} datum as a query that touches the same index once and reads 1 row. Per-row attribution still requires performance_schema for fine-grained analysis.

Seen in

  • sources/2026-04-21-planetscale-tracking-index-usage-with-insights — Rafer Hazen (2024-08-14) canonicalises the InnoDB index_init handler-API callback as the intercept point for per-query index-usage capture. Patched into PlanetScale's MySQL fork; populates a concepts/per-query-used-index-set; results ride along in the final MySQL wire-protocol packet to VTGate; aggregated per fingerprint and emitted into the Insights pipeline every 15 seconds. 100% coverage at "negligible overhead". The feature is the substrate for Insights' Indexes tab, index:table.index search predicate, and indexed:false unindexed-query filter.
Last updated · 470 distilled / 1,213 read