Mastering SQL EXPLAIN: How to Analyze and Optimize Queries

Usage

To analyze a SQL statement, prepend EXPLAIN to the query, e.g.,

EXPLAIN SELECT * FROM user;

Result Fields Explanation

id : Query identifier.

select_type : Type of SELECT, possible values include:

simple – no subqueries

primary – contains subqueries or derived tables

subquery – subquery not in FROM

derived – subquery in FROM

union

union result

table : Table the row refers to; can be a table name, alias, derived, or null.

possible_key : Keys that could be used.

key : Key actually used.

key_len : Length of the key (shorter is better).

type : Join type, ordered from best to worst:

system

const

eq_ref

ref

fulltext

ref_or_null

index_merge

unique_subquery

index_subquery

range

index

ALL

ALL indicates a full table scan, which is inefficient. index scans index nodes sequentially. range performs range scans. ref uses an index to locate a range. eq_ref locates a single row via index. const and system represent constant-time lookups, often seen with primary key queries.

ref : Relationship between tables in a join.

rows : Estimated number of rows examined.

extra : Additional information, such as:

using index – the query uses only the index

using where – a WHERE clause is applied

using temporary – a temporary table is created

using filesort – a filesort operation is performed

range checked for each record – each record is checked against a range