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.
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.
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.
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 |