<h2 class="blockquote">Overview</h2>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.<img class="aligncenter wp-image-10292" src="https://storage.googleapis.com/storage-codeclouds/medium/Tip-of-The-Day-Explain-in-MySQL-02.png?updated_at=2023-07-27T05:23:11.679Z" alt="MySQL Explain Query" width="" height="">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:<img class="aligncenter wp-image-10292" src="https://storage.googleapis.com/storage-codeclouds/xlarge/EXPLAIN.png?updated_at=2023-07-27T05:23:11.806Z" alt="MySQL Explain Output" width="687" height="126">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 rowtype – 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 allpossible_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.&nbsp;&nbsp;&nbsp; &nbsp;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.<img class="blog-img-below-gap" src="https://storage.googleapis.com/storage-codeclouds/large/Tip-of-The-Day-Explain-in-MySQL.png?updated_at=2023-07-27T05:23:10.185Z" alt="web developer jobs in kolkata" width="" height=""> &nbsp;&nbsp; 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 <a href="/blog/study-benefits-nosql-sql/">comparison of SQL and NoSQL databases</a>. If you’re looking for work, check out our jobs page—we have plenty of <a target="_blank" rel="noopener noreferrer" href="https://careers.codeclouds.com/jobs/web-developer/">web developer jobs in Kolkata</a> available, and we’d love to have you on the CodeClouds team.

In this blog, we go over the ever so handy EXPLAIN query in MySQL, and give a detailed rundown of the output of the query

Tip of The Day: Explain in MySQL

large_sales-thumb.webp

medium_sales-thumb.webp

small_sales-thumb.webp

thumbnail_sales-thumb.webp

sales-thumb.webp

thumbnail_Josh-Warfel.jpg

Josh-Warfel.jpg

thumbnail_Spencer-Leatherman.jpg

Spencer-Leatherman.jpg

How To Switch From Manual To Automated Testing As a Tester

An intro to Docker, Basic Image Setup, and Deploying Your First Container

Junior Developer vs Senior Developer: What’s the Difference?

7 Things to Know When Working From Home

9 Guides to Help You Prepare for Your Software Developer Interview

The Advantages and Disadvantages of Using GitHub

Docker, Kubernetes, and Helm: How To Up Your Containerization Game

What Will JavaScript Frameworks Look Like in the Next 10 Years?

The Differences Between MongoDB and SQLite

How to Insert Custom PHP to a WordPress Post

Why You Should Consider a Dedicated Offshore Development Team

Top 10 Website Designing Tips!

Seven steps to building the ultimate remote development team

Magento, Shopify, and BigCommerce: Which eCommerce Platform is Right for You?

How to Create a Better Subscription Business with an Alternative to Shopify Subscription Apps!

Python

React

Node.js

Java

Android

QA Engineers

Software Testers

thumbnail_Aaron_Rhodes_blue.jpg

Aaron_Rhodes_blue.jpg

thumbnail_Scott_Hunley.jpg

Scott_Hunley.jpg

prowriter

puppy-dogs-icecream

aid-of-indiana

chateau-divoire

netreputation

singers-company

CodeClouds

facebook

youtube

twitter

linkedin

large_aboutus_india.jpg

medium_aboutus_india.jpg

small_aboutus_india.jpg

thumbnail_aboutus_india.jpg

aboutus_india.jpg

ind-90px.svg

large_globsyn-crystals.webp

medium_globsyn-crystals.webp

small_globsyn-crystals.webp

thumbnail_globsyn-crystals.webp

globsyn-crystals.webp

Globsyn Crystals (HQ)

large_salt-lake.jpg

medium_salt-lake.jpg

small_salt-lake.jpg

thumbnail_salt-lake.jpg

salt-lake.jpg

Signet Tower

large_newtown.jpg

medium_newtown.jpg

small_newtown.jpg

thumbnail_newtown.jpg

newtown.jpg

Candor Tech Space

large_findus-usa.png

medium_findus-usa.png

small_findus-usa.png

thumbnail_findus-usa.png

findus-usa.png

usa-90px.svg

large_fort-wayne.jpg

medium_fort-wayne.jpg

small_fort-wayne.jpg

thumbnail_fort-wayne.jpg

fort-wayne.jpg

Fort Wayne

large_findus-nz.webp

medium_findus-nz.webp

small_findus-nz.webp

thumbnail_findus-nz.webp

findus-nz.webp

nzl-90px.svg

large_wellington.jpg

medium_wellington.jpg

small_wellington.jpg

thumbnail_wellington.jpg

wellington.jpg

Wellington

large_findus-aus.png

medium_findus-aus.png

small_findus-aus.png

thumbnail_findus-aus.png

findus-aus.png

aus-90px.svg

large_sydney.jpg

medium_sydney.jpg

small_sydney.jpg

thumbnail_sydney.jpg

sydney.jpg

Sydney