MySQL Explain Query and Execution Plan Example

EXPLAIN in MySql is one of the strongest feature tool through which we can analyze and optimize the Query and we can rewrite in a cost efficient and resource Optimized way which can execute faster.

Basically EXPLAIN will give you the information about the Query Execution Plan which is used. Means it will give the information about the way through which Database execute Query. It can be used in front of SELECT, INSERT, UPDATE, DELETE and REPLACE. It is a feature tool through which we can find the Quick remedy for Slow Query.

If your MySql EXPLAIN Query is not working or not Parsing correctly then you can type SHOW WARNINGS into your editor and run it.

Use  EXPLAIN to Improve Query Performance

Prameter	Purpose
id	SQL Query id
select_type	Type of SQL Statement
table	Referenced Table
type	Type of Join (It defined how the table will be accessed or joined)
possible_keys	Possible key which can be  used in the Query
key	Key which is used in the Query
key_len	Used Key length in the Query
ref	Columns which got compared to indexes
rows	Numbe of rows scanned during query execution
Extra	Additional Information about Query

Consider the below Query (We have studentDetails Table which have millions of record in it ) :

EXPLAIN SELECT * FROM `studentDetails`

id	1
select_type	SIMPLE
table	studentDetails
type	ALL
possible_keys	(NULL)
key	(NULL)
key_len	(NULL)
ref	(NULL)
rows	40846
Extra

Above will be the output of the Query which we tried Executing using EXPLAIN. Usually EXPLAIN will give you lot of information regarding your query like which key are used, Indexing is used or not, how many rows are getting scanned while executing query, what are all the possible keys we can use in query etc.. We will study the above in detail below.

We will go through the each id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra column parameter in detail. And last we will analyze the Explain Query Example. 

select_type ( Type of SQL Statement ) :  Possible value which can come based on the Query is below

• SIMPLE
• PRIMARY, DERIVED 
• SUBQUERY 
• DEPENDENT SUBQUERY 
• UNCACHEABLE SUBQUERY 
• UNION 
• DEPENDENT UNION 
• UNION RESULT

Most important point to look in the EXPLAIN Query output is type and rows. Row gives you the information about how many rows are scanned before the query output. Type define how the table will be access or joined. We will see EXPLAIN Query type  in detail below.

• system : The table contain one or Zero rows.
• const : It is fastest type of join. It will have only one matching rows which will be indexed. It is fastest because table is scanned once and the column is treated as constant while joining other table.
• eq_ref :  In this Index (Primary or Unique NOT NULL) will be used by the join and it is the best table join type. One rows will read from the table and compared with rows from the previous table.
• ref
• fulltext :  This join will use FULLTEXT index for processing the query.
• ref_or_null
• index_merge
• unique_subquery
• range : Only rows belong to some particular or specified range get scanned for the query output. DB indexing is used to select or find the matching rows.
• index : In this all Index Tree will be scanned to find the exact matched query condition. 
• all : Entire Table row and its combination will be scanned to find the matched query condition output. 

Now we can see some Example :

EXPLAIN SELECT * FROM users WHERE id >60000 AND id < 60002

Prameter	Value
id	1
select_type	SIMPLE
table	users
type	range
possible_keys	id,registered_user,id_attyp
key	id
key_len	4
ref	(NULL)
rows	1
Extra	Using where