MySQL EXPLAIN explained

Below is my notes from watching the MySQL’s EXPLAIN demystified webinar. All credits go to Baron Schwartz for this excellent intro to MySQL’s query explain. How does MySQL execute queries? SQL => Parse Tree => Execution plan The execution plan is a data structure, not byte-code The executor makes storage engine calls Execution plan “Deep left tree” – always Explain output columns id which SELECT the row belongs to Labelled sequentially complex select: subquery: numbered according to the position in SQL text derived: executed as a temporary table union: fill a temp table, then read out with a NULL id select_type simple: there’s only one SELECT in the whole query, select_type is PRIMARY complex: subquery: numbered according to the position in SQL text derived: executed as a temporary table union: fill a temp table, then read out with a NULL id table table name or alias <DerivedN>, N correspond to id <unionM,N>, M, N correspond to id type describes how MySQL will access the rows Possible values: ALL: table scan index: full index scan range: range of an index ref: value as a reference to look into an index if rows in the index match the value eq_ref: like ref but unique (unique index or PK) const system: does not require accessing a table, e.