MySQL EXPLAIN Visualizer

Paste your MySQL EXPLAIN or EXPLAIN FORMAT=JSON output and get a color-coded query plan with actionable warnings. Full table scans (type=ALL), missing indexes (key=NULL), filesorts, and temporary tables are flagged immediately. A short verdict at the bottom summarizes what to fix and where to add indexes.

How to use the MySQL EXPLAIN Visualizer

Run EXPLAIN SELECT ... or EXPLAIN FORMAT=JSON SELECT ... in your MySQL client, copy the output, and paste it here. Click Analyze Plan.

  1. Tabular EXPLAIN — the classic pipe-delimited table MySQL CLI prints. The visualizer parses each row and renders a clean HTML table with color-coded warnings: red for full scans (type=ALL), orange for no index used (key=NULL), yellow for large row estimates, pink for Using filesort or Using temporary in the Extra column.
  2. FORMAT=JSON — the structured JSON output from EXPLAIN FORMAT=JSON. The visualizer walks the query_block tree, extracts table access details, and shows access_type, key, rows_examined_per_scan, cost_info, and the same warnings.
  3. Verdict — a one-line summary at the top tells you how many problems were found and which tables need attention. Use the per-row warning messages to guide index creation.

What does MySQL EXPLAIN show and why does it matter?

EXPLAIN is MySQL's query plan inspector — it shows how the optimizer intends to execute a SELECT (or UPDATE/DELETE) without actually running it. Each row in the output represents one step in the execution plan: a table access, a subquery, or a derived table. The most important columns are type (the join type, from worst ALL to best const/eq_ref), key (which index the optimizer chose, or NULL if none), rows (the optimizer's estimate of rows examined), and Extra (additional notes like Using filesort, Using temporary, Using index).

The join type hierarchy matters: ALL is a full table scan — every row examined, scales linearly with table size, and is almost always a bug for large tables. index is a full index scan (better, but still scans all index entries). range scans a subset of an index (good). ref/eq_ref uses an equality lookup on an index (excellent). const/system resolves to a single row at query planning time (best possible). Using filesort means MySQL must sort result rows in memory or on disk — it cannot use the index order for the ORDER BY. Using temporary means MySQL creates a temporary table, often for GROUP BY or DISTINCT on non-indexed columns.

EXPLAIN FORMAT=JSON (available since MySQL 5.6) provides the same information in structured form with additional detail: cost_info (optimizer cost estimates), rows_examined_per_scan (per-loop row estimates for nested-loop joins), and used_columns. For complex queries with subqueries or derived tables, the JSON form is easier to navigate than the flat tabular output.

Common use cases

  • Identifying full table scans — spot type=ALL rows that will degrade under load and determine which columns need an index.
  • Index audit — confirm that a newly added index is actually being picked up by the optimizer (key column changes from NULL to the index name).
  • Query optimization — diagnose why a query is slow by checking rows estimates, filesort, and temporary table usage before profiling in production.
  • ORM-generated query review — ORMs often generate queries that hit full scans on FK columns or use subqueries that create temporary tables; EXPLAIN exposes these.
  • Pre-deploy query checks — run EXPLAIN on new queries in staging as part of a deployment checklist to catch slow paths before they hit production traffic.

Frequently asked questions

What is the worst EXPLAIN type value I should watch for?
type=ALL is a full table scan and is almost always the highest priority fix for large tables. type=index is a full index scan — better than ALL but still problematic at scale. For any hot query path, aim for range, ref, eq_ref, or const.

When is Using filesort actually a problem?
Using filesort means MySQL cannot use an index to satisfy the ORDER BY. For small result sets (a few hundred rows) it is usually fast. For large result sets or frequent queries, add a composite index that covers both the WHERE columns and the ORDER BY columns in the correct order.

Why does key=NULL appear even when I have an index?
The optimizer skips an index when it estimates a full scan is cheaper (e.g. the query returns more than ~30% of rows, or statistics are stale). Run ANALYZE TABLE to refresh statistics. Also check that the index covers the query's WHERE columns exactly — a leading-column mismatch means the index cannot be used.

What does filtered mean in the EXPLAIN output?
filtered is a percentage estimate of the rows that will pass the WHERE conditions after the storage engine hands them to MySQL. A value of 100 means all returned rows pass the filter (index is selective). A value of 10 means 90% of rows fetched from the index are discarded, suggesting the index is not very selective for this query.

Is EXPLAIN FORMAT=JSON better than the tabular format?
For simple queries, both formats contain the same information. For complex queries with subqueries, derived tables, or CTEs, FORMAT=JSON is significantly more readable because it reflects the nested execution structure rather than flattening everything into rows. The cost_info field (only in JSON) also shows the optimizer's cost estimates for each step.