Master MySQL EXPLAIN: Decode Execution Plans for Faster Queries

What EXPLAIN Does

When EXPLAIN is used with an SQL statement, MySQL shows how the optimizer will execute the query, including table load order, index usage, and join order.

EXPLAIN Output Columns

id : Execution order of SELECT statements; larger values run earlier.

select_type : Type of SELECT (SIMPLE, PRIMARY, SUBQUERY, DERIVED, UNION, etc.).

table : Table name or alias; may be a derived temporary table.

partitions : Partition(s) used; NULL for non‑partitioned tables.

type : Join type, from best to worst – system, const, eq_ref, ref, ref_or_null, index_merge, unique_subquery, index_subquery, range, index, ALL.

possible_keys : Indexes that could be used.

key : Index actually used; NULL if none.

key_len : Length of the used index in bytes.

ref : Columns or constants used with the index.

rows : Estimated rows examined.

filtered : Approximate percentage of rows that pass the filter.

Extra : Additional info such as Using index, Using where, Using temporary, Using filesort, Using join buffer, Impossible WHERE, No tables used.

Key EXPLAIN Fields Explained

id

Higher id values have higher priority; identical ids mean the optimizer decides the order.

select_type

Distinguishes simple queries (SIMPLE) from complex ones (PRIMARY, SUBQUERY, DERIVED, UNION, etc.).

type

Shows how MySQL accesses rows; system is fastest, ALL is a full table scan.

Extra Examples

mysql> SELECT version() FROM dual;

Result:

+------------+
| version()  |
+------------+
| 5.7.17-log |
+------------+

Creating three tables and joining them:

CREATE TABLE one (one_id INT PRIMARY KEY, one_name VARCHAR(20));
CREATE TABLE two (two_id INT PRIMARY KEY, two_name VARCHAR(20));
CREATE TABLE three (three_id INT PRIMARY KEY, three_name VARCHAR(20));
-- Join condition: one.two_id = two.two_id AND two.three_id = three.three_id

Running EXPLAIN on a join:

EXPLAIN SELECT * FROM one o, two t, three r WHERE o.two_id = t.two_id AND t.three_id = r.three_id;

Typical output shows type = ALL for the first table, Using where; Using join buffer (Block Nested Loop) for subsequent tables, indicating missing indexes.

Common Extra Flags

Using index

: Covered index; query can be satisfied from the index alone. Using where: Rows filtered after index lookup. Using temporary: Temporary table needed for GROUP BY or ORDER BY. Using filesort: MySQL must sort rows without an index. Using join buffer: Join performed without an index, using a buffer. Impossible WHERE: Condition can never be true. No tables used: Query does not reference any table (e.g., SELECT NOW();).

Practical Takeaways

Understanding EXPLAIN helps diagnose slow queries by revealing which indexes are used, how many rows are scanned, and whether additional operations like temporary tables or filesorts are required. Optimizing based on this information—adding appropriate indexes, rewriting subqueries, or avoiding full scans—leads to faster, more efficient SQL.