Mysql EXPLAIN output format:Explaining the EXPLAIN output: Part 1:
December 6, 2011 Leave a comment
The EXPLAIN commands has the following 10 output columns (from:7.8.2. EXPLAIN Output Format)
ColumnMeaning
idThe SELECT identifier
select_typeThe SELECT type
tableThe table for output row
typeThe join type
possible_keysThe possible indexes to show
keyThe index actually chosen
key_lenThe length of chosen key
refThe columns compared to the index
rowsEstimate of rows to be examined
ExtraAdditional information
Lets start explaining each of these columns one by one with examples:
id
The SELECT identifier. This is the sequential number of the SELECT within the query.
EXPLAIN SELECT * FROM table1 WHERE name='name0' UNION SELECT * FROM table1 WHERE name='name1';
“The query above is a dumb query”.
*select_type will be explained later
The result of the query will have 3 rows:There will be 2 id’s generated:
1] id=1 for select * from table1 where name=’name0′ with
select_tye:PRIMARY, table:table1;
2] id=2 for select * from table1 where name=’name1′ with
select_tye:UNION, table:table1;
3] id=NULL. No sequential numbers will be generated here;
select_tye:UNION RESULT, table:;
select_type
SIMPLE
Simple SELECT (not using UNION or subqueries)
EXAMPLE QUER(Y|IES):
SELECT * FROM TABLE1;
SELECT * FROM TABLE1 WHERE COLUMN1='SOMESTRING';
PRIMARY
Outermost SELECT
EXAMPLE QUER(Y|IES):
SELECT * FROM table1 WHERE name='name0' UNION SELECT * FROM table1 WHERE name='name1';
SELECT * FROM table1 is the outer most select hence PRIMARY.
SELECT ID FROM (select * from TABLE1 where NAME='name0') p;
SELECT ID FROM is the outer most select hence PRIMARY.
UNOIN
Second or later SELECT statement in a UNION
EXAMPLE QUER(Y|IES):
SELECT * FROM table1 WHERE name='name0' UNION SELECT * FROM table1 WHERE name='name1';
SELECT * FROM table1 WHERE name=’name1′ is the second select in the above query with UNION hence select_type is UNION
SELECT * FROM table1 WHERE name='NAME0' UNION SELECT * FROM name WHERE name='NAME1' UNION SELECT * FROM table1 WHERE name='name2';
SELECT * FROM table1 WHERE name=’name1′ is the second select; SELECT * FROM table1 WHERE name=’name1′ is the later select; in the above query with UNION hence select_type for these 2 selects will be UNION
DEPENDENT UNION
Second or later SELECT statement in a UNION,dependent on outer query
EXAMPLE QUER(Y|IES):
SELECT t1.column_name,(SELECT t2.column_name FROM table2 AS t2 WHERE t2.name='NAME0' UNION SELECT column_name FROM table3 WHERE id=t1.id) FROM table1 AS t1 WHERE name='name1';
SELECT column_name FROM table3 WHERE id=t1.id has the select_type as DEPENDENT UNION as the where clause in it is dependent on a value from an outer query which is:SELECT t1.column_name,… FROM table1 AS t1 WHERE name=’name1′;
UNION RESULT
Result of a UNION.
Any query which has a UNION in it will have this row(extra) in its explain output with an id NULL and select_type as UNION RESULT
SUBQUERY
First SELECT in subquery
for more about SUBQUERY visit here:12.2.10. Subquery Syntax
EXAMPLE QUER(Y|IES):
SELECT t1.column_name,(SELECT t2.column_name FROM table2 AS t2 WHERE t2.name='NAME0') FROM table1 AS t1 WHERE t1.name='NAME0';
SELECT t2.column_name FROM table2 AS t2 WHERE t2.name=’NAME0′ is the sub query which is not dependant on any other query.
DEPEDENT SUBQUERY
First SELECT in subquery, dependent on outer query
EXAMPLE QUER(Y|IES):SELECT t1.column_name,(SELECT t2.column_name FROM table2 AS t2 WHERE t2.id=t1.id) FROM table1 AS t1 WHERE t1.name='name0';
SELECT t2.column_name FROM table2 AS t2 WHERE t2.id=t1.id has a where clause which is dependent on the outer query: SELECT t1.column_name,…FROM table1 AS t1 WHERE t1.name=’name0′; hence its select_type will be DEPENDENT SUBQUERY
DERIVED
Derived table SELECT (subquery in FROM clause)
EXAMPLE QUER(Y|IES):
SELECT id FROM (SELECT * FROM table1 WHERE name like 'abcd') AS t1;
SELECT * FROM table1 WHERE name like ‘abcd’ is a SUBQUERY which lies in the FROM clause hence has the select_type as DERIVED
UNCACHEABLE SUBQUERY
A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
The EXAMPLE QUER(Y|IES)
SELECT t1.column_name,(SELECT t2.column_name FROM table2 AS t2 WHERE t2.name='name0' UNION SELECT t3.column_name FROM table3 AS t3 WHERE t3.id=t1.id ) FROM table1 AS t1 WHERE name='NAME0';
SELECT t2.column_name FROM table2 AS t2 WHERE t2.name=’name0′ will have the select_type as UNCACHEABLE SUBQUERY since its in conjunction in an UNION with SELECT t3.column_name FROM table3 AS t3 WHERE t3.id=t1.id which has a where clause dependent on the results from an outer query and have select_type as DEPENDENT UNION.
UNCACHEABLE UNION
The second or later select in a UNION that belongs to an uncacheable subquery(see UNCACHEABLE SUBQUERY)
SELECT t1.column_name,(SELECT t2.column_name FROM table2 AS t2 WHERE t2.id=t1.id UNION SELECT t3.column_name FROM table3 AS t3 WHERE t3.name='NAME0' ) FROM table1 AS t1 WHERE name='NAME0';
SELECT t3.column_name FROM table3 AS t3 WHERE t3.name=’NAME0′ will have its select_type as UNCACHEABLE UNION as it is the 2nd query in the UNION with the query: SELECT t2.column_name FROM table2 AS t2 WHERE t2.id=t1.id which has a where clause dependent on the results of an outer query hence its select_type being DEPENDENT SUBQUERY.
For more about query caching, read this: MySQL Query Cache
table
Displays the table name for that output row.
SELECT t1.column_name,(SELECT t2.column_name FROM table2 AS t2 WHERE t2.id=t1.id UNION SELECT t3.column_name FROM table3 WHERE name='NAME0' ) FROM table1 AS t1 WHERE name='NAME0';
The above code will the following table name listed
t1
t2
table3
*will be continued in the next part.
*find any mistakes? do feel free to point it out.