Posts

Tip of The Day: Explain in MySQL

“EXPLAIN” in MySQL has a lot in the store and makes it a wonderful choice for the developers. Moreover, when you are using “EXPLAIN”, it can promisingly help you to optimize many troublesome MySQL queries making the work easier for many. This blog is about the valuable information that you need to know while using it in MySQL.

When we run a query, the MySQL Query Optimizer tries to devise an optimal plan for query execution. We can see information about the query plan by prefixing the query with EXPLAIN. EXPLAIN helps us understand and optimize troublesome MySQL queries.

MySQL’s explain plan which tends to give a false sense of safety because it says too much about the indexes being used. The most important information is available in the TYPE column of MySQL’s explain output. However, even there the keyword INDEX does not indicate proper indexing.

It shows several bits of useful information such as..

MySQL Explain

1. id – A sequential identifier for each SELECT within the query (if you have nested subqueries)

2. select_type – The type of SELECT query. Possible values are:
SIMPLE, PRIMARY, DERIVED, SUBQUERY, DEPENDENT SUBQUERY, UNCACHEABLE SUBQUERY, UNION, DEPENDENT UNION, UNION RESULT etc.

3. table – The table referred to by the row

4. type – Indicates missing indexes or how the query that is written should be reconsidered. Possible values are:
system, const, eq_ref, ref, fulltext, ref_or_null, index_merge, unique_subquery, index_subquery,
range, index or all

5. possible_keys – Shows the keys that may or may not be used in practice to find rows from the table. If the column is NULL, it indicates no relevant indexes could be found.

6. key – Indicates the actual index used by MySQL, this is often the optimal choice.

7. key_len – Indicates the length of the index the Query Optimizer chose to use.

8. ref – Shows the columns or constants that are compared to the index named in the key column. MySQL will either pick a constant value to be compared or a column itself based on the query execution plan.

9. rows – Lists the number of records that were traversed to produce the output.

10.Extra – Contains additional information regarding the query execution plan. Values such as “Using temporary”, “Using filesort”, etc. in this column may indicate a troublesome query.
Explain Extended: Helps us see the query that is executed after any transformations have been made by the Query Optimizer.

MySQL EXPLAIN