All about Programming

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:

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" /]