MySQL Explain Query and Execution Plan Example

EXPLAIN in MySql with 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


Our Feature Post

There is a tree between houses of A and B If the tree leans on As House

    There is a tree between houses of A and B. If the tree There is a tree between houses of A and B. If the tree leans on A’s House, the t...

Our Popular Post