Explain Keyword and SQL Execution Plan in MySQL

SQL Execution Plan: You can see the SQL Execution Plan by using “Explain” keyword. It tells how query is executed.

Understanding the Query Execution Plan in MySQL
Depending on the details of your tables, columns, indexes, and the conditions in your WHERE clause, the MySQL optimizer considers many techniques to efficiently perform the lookups involved in an SQL query. A query on a huge table can be performed without reading all the rows; a join involving several tables can be performed without comparing every combination of rows. The set of operations that the optimizer chooses to perform the most efficient query is called the “query execution plan”, also known as the EXPLAIN plan. Your goals are to recognize the aspects of the EXPLAIN plan that indicate a query is optimized well, and to learn the SQL syntax and indexing techniques to improve the plan if you see some inefficient operations.
ref: http://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html

To explain this lets take the example of simple employee department relationship.

Here we have schema with name employee_schema with following tables;

department
department

employee
employees

dept_emp
dept_emp

Now lets join 3 tables employee, dept_emp and department with a where clause

explain select * from employees e 
inner join dept_emp de on de.emp_id1 = e.emp_id
inner join department d on d.department_id=dept_id
where e.emp_id=1

and the output will be
explain with where clause

Understanding the output
* Output contains information of each table involve in the query in a form of a row. Means a row gives information of a table.
* Output is in ordered form, means output is in the order that MySQL would read them while processing the statement

Column 1: (id)
“id” is the SELECT identifier. This is the sequential number of the SELECT within the query. But what that means?

To understand this let’s do some modification in the query

explain select * from employees e 
inner join dept_emp de on de.emp_id1 = e.emp_id
inner join department d on d.department_id=dept_id
where e.emp_id = (select max(emp_id) from employees)

subquery

Column 2: (select type)
As its name suggest it give you the type of select statement. Depending on the query select type can be following

Column 3: (table)
This column shows the table to which the row of output refers.

Column 4: (type)
This column tells the type of join. All join types

Column 5: (Possible keys)
This column show the all possible keys that can be used by the MySQL to process the query within the table.

Column 6: (key)
Actually used key from the all possible keys

Column 7: (key_len)
Give the length of the key that is used in column 6.
The key_len column indicates the length of the key that MySQL decided to use. The length is NULL if the key column says NULL. Note that the value of key_len enables you to determine how many parts of a multiple-part key MySQL actually uses.

Column 8: (ref)
The ref column shows which columns or constants are compared to the index named in the key column to select rows from the table.

Column 9: (rows)
The rows column indicates the number of rows MySQL believes it must examine to execute the query.
For InnoDB tables, this number is an estimate, and may not always be exact.