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