MySql Explain Tutorial

By | August 8, 2012

This tutorial will demonstrate the MySQL tool explain which is a great tool offered by mysql to analzye queries for further improvement. To run the explain tool you just have to type the command EXPLAIN right before your select query and let MySQL execute it.
MySQL will not execute the select query but will analyze it and display the result.

A simple select

We will start with a simple select query counting all the male employees.

mysql> SELECT COUNT(*) FROM employees WHERE gender = 'M';
+----------+
| COUNT(*) |
+----------+
|   179973 |
+----------+
1 ROW IN SET (0.25 sec)

Now we will use the EXPLAIN command to get the information how the data for this query is fetched:
Example:

mysql> EXPLAIN SELECT COUNT(*) FROM employees WHERE gender = 'M';
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | TABLE     | TYPE | possible_keys | KEY  | key_len | REF  | ROWS   | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 299809 | USING WHERE |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

Lets take a look on the output to understand what MySQL is telling us. For every select, subselect or join EXPLAIN will output one row with information how the data for this part of the query will be retrieved if you execute the query. In this case it tells us that the first query is a simple select on the table emplyees. possible_keys=NULL tells us that there was no key which MySQL could choose from and key=null means that no key was used to retrieve the data. The field rows gives us the number of rows which has to be examined to get the data. In an ideal case rows should be equal with the number of results you expect.
For this select there is no key which can be used, so a full table scan will be done.

To improve this query we add an index on gender, and we run the query and the EXPLAIN again. To get real performance data
the query caching has been disabled using SET SESSION query_cache_type = OFF.

mysql> ALTER TABLE employees ADD INDEX index_gender(gender);
Query OK, 300024 ROWS affected (6.37 sec)
Records: 300024  Duplicates: 0  Warnings: 0
 
mysql> SELECT COUNT(*) FROM employees WHERE gender = 'M';
+----------+
| COUNT(*) |
+----------+
|   179973 |
+----------+
1 ROW IN SET (0.18 sec)

As we can see the query was executed faster, lets see how the result of EXPLAIN has changed.

mysql> EXPLAIN SELECT COUNT(*) FROM employees WHERE gender = 'M';
+----+-------------+-----------+------+---------------+--------------+---------+-------+--------+--------------------------+
| id | select_type | TABLE     | TYPE | possible_keys | KEY          | key_len | REF   | ROWS   | Extra                    |
+----+-------------+-----------+------+---------------+--------------+---------+-------+--------+--------------------------+
|  1 | SIMPLE      | employees | REF  | index_gender  | index_gender | 1       | const | 150283 | USING WHERE; USING INDEX |
+----+-------------+-----------+------+---------------+--------------+---------+-------+--------+--------------------------+

This time MySQL found the key index_gender and used it to retrieve the data. Instead of the full table scan it only had to examine
half of the rows to execute the query this time.

Detail explaination of all fields

id
The SELECT identifier. This is the sequential number of the SELECT within the query.

select_type

The type of SELECT, which can be any of those shown in the following table:

SIMPLE Simple SELECT (no UNION / subqueries)
PRIMARY Outermost SELECT
UNION Second or later SELECT statement in a UNION
DEPENDENT UNION Second or later SELECT statement in a UNION, dependent on outer query
UNION RESULT Result of a UNION.
SUBQUERY First SELECT in subquery
DEPENDENT SUBQUERY First SELECT in subquery, dependent on outer query
DERIVED Derived table SELECT (subquery in FROM clause)

table
The table to which the row of output refers.

type
The join type

possible_keys
The possible_keys column indicates which indexes MySQL can choose from use to find the rows in this table.

Key
The key column indicates the key (index) that MySQL actually decided to use. The key is NULL if no index was chosen. To force MySQL to use or ignore an index listed in the possible_keys column, use FORCE INDEX, USE INDEX, or IGNORE INDEX in your query

key_len
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

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.

rows
The rows column indicates the number of rows MySQL believes it must examine to execute the query.

Extra

This column contains additional information about how MySQL resolves the query

More information about EXPLAIN

http://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html

[amazon_carousel widget_type="SearchAndAdd" width="600" height="200" title="" market_place="" shuffle_products="False" show_border="False" keywords="MySQL" browse_node="" search_index="Books" /]

2 thoughts on “MySql Explain Tutorial

  1. Bryan Alsdorf

    Shameless plug though it is related. MariaDB has an explain analyzer which can be used to parse, highlight and share explains. Some of the columns / values are clickable to give a short explanation of what something means.

    http://mariadb.org/ea/5eccr

    If you have any improvement suggestions, please let me know.

    Reply

Leave a Reply