How do you read EXPLAIN output in MySQL?

Summary

Look at type (access method, from best const/eq_ref/ref/range to worst ALL = full scan), key (the index chosen), rows (estimated rows examined), filtered, and Extra (Using index, Using where, Using filesort, Using temporary).

Longer Answer

• type = ALL on a large table means a full table scan. index means a full index scan (better, but still everything).
• possible_keys vs key: an index exists but was not chosen, often because of low selectivity or a type mismatch.
• rows x filtered estimates how many rows flow to the next join step.
• Using filesort: an extra sort step (not necessarily on disk); often fixed with an index matching ORDER BY.
• Using temporary: an internal temporary table, common with GROUP BY or DISTINCT on non-indexed columns.

EXPLAIN FORMAT=TREE shows the plan as a tree, and EXPLAIN ANALYZE (8.0.18+) actually runs the query and reports real row counts and timings per step, which reveals wrong estimates.

Key Points

• type: const > eq_ref > ref > range > index > ALL.
• key, rows, filtered.
• Using filesort / Using temporary are warning signs.
• EXPLAIN ANALYZE shows actual timings.

What interviewers look for

The ability to turn EXPLAIN output into an action (which index to add, which query to rewrite).

Follow-up questions

• What does Using index condition mean?