CodeClouds Color Logo

DEVELOPMENT TOOLS, TIP OF THE DAY | 23 May 2017

Tip of The Day: Explain in MySQL

The mySQL EXPLAIN query is an absolute godsend. What does it do? Exactly what it says on the tin: it explains how a particular query works to help you better understand complex (or poorly-written!) queries and better get to grips with your specific database. Today we’re talking about how to get the most out of it

MySQL Explain Query

So firstly, EXPLAIN isn’t a query in-and-of itself; it’s a sort of meta-query that you stick in front of other queries to help see what’s going on under the hood. So, take this simple query:

SELECT * FROM myCoolDB WHERE myCoolDB.myFunTable = ‘This is an example’

and run EXPLAIN on it:

EXPLAIN SELECT * FROM myCoolDB WHERE myCoolDB.myFunTable = ‘This is an example’

and MySQL will spit out an execution plan guiding you through the order it executes the query in. It’ll look something like this:

MySQL Explain Output

Normally the execution path is pretty obvious and EXPLAIN is overkill, but there are times when it’s suddenly exactly what you need. Sometimes, you crack open a database and find a query that has been iterated upon by seven engineers over 20 years, that takes up 30 lines of code and has nested queries within queries within queries and possibly a portal to R’lyeh. That’s when you need EXPLAIN.

The components of an EXPLAIN output are as follows:

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

select_type –The type of SELECT query. Possible values are:

SIMPLE, PRIMARY, DERIVED, SUBQUERY, DEPENDENT SUBQUERY, UNCACHEABLE SUBQUERY, UNION, DEPENDENT UNION, UNION RESULT etc.

table – The table referred to by the row

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

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.

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

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

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.

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

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.

web developer jobs in kolkata
And that’s it, for now. EXPLAIN isn’t right for every situation, but it has a huge amount of utility for certain cases, and it can really save your bacon when dealing with larger queries. If you’re a database engineer looking to improve their knowledge-base, read our comparison of SQL and NoSQL databases. If you’re looking for work, check out our jobs page—we have plenty of web developer jobs in Kolkata available, and we’d love to have you on the CodeClouds team.

Originally written May 23, 2017. Last updated September 8th, 2020

 Views

Written by Biplab Pal

Biplab is a technology enthusiast. After completing his Masters in Systems Management in 2007, he worked in many domains. In web technology he has more than six years of experience.

  • facebook
  • twitter
  • linkedin
  • pinterest
  • whatsup

Related Articles

Improving Your Site’s Load Time With PageSpeed Insights

DEVELOPMENT TOOLS | 28 July 2020

Improving Your Site’s Load Time With PageSpeed Insights

Web optimization has something we call the “three second rule”—if your site takes more than three seconds to load, then you’re in serious trouble. Don’t worry though, today we’re talking technique, and specifically about the right way to use Google PageSpeed Insights.

Practical Development: Can a Programmer Be Emotional?

TIP OF THE DAY, DEVELOPER RESOURCES | 11 June 2020

Practical Development: Can a Programmer Be Emotional?

One of the most important skills an engineer can develop is being able to keep a clear head when everything is falling apart. Today we’re talking about techniques that can help you be a practical and effective developer.