EmergencyEMERGENCY? Get 24/7 Help Now!

Extended EXPLAIN

 | July 24, 2006 |  Posted In: Insight for Developers


One nice feature added for EXPLAIN statement in MySQL 4.1 is EXTENDED keyword which provides you with some helpful additional information on query optimization. It should be used together with SHOW WARNINGS to get information about how query looks after transformation as well as what other notes optimizer may wish to tell us. It is best seen by example:

As you see after query transformation “*” was expanded to full column list, each column was fully quantified plus MySQL optimizer converted
c=”a” and pad=c; to c=”a” and pad=”a”. MySQL optimizer could not however catch id>5 and id>6 can be converted just to id>6.

Lets see another example:

In this case we can see table t1 is accessed using “const” access type, which means it should pre-read value for “t1.k” and place it into the query. This transformation however is not shown by EXTENDED EXPLAIN for some reason, while it would be quite helpful.

Finally lets look at third example:

As you see in this case there is planty of extra information in the message. We can see query is using , it is converted to using . And there is some in being done.

Unfortnuately there is yet not too much does available about what each of these means so you should guess from names or take a look at the sources.

To note about last query – even if it looks like there is a whole big deal of optimization being done query is executed extremely poor, completing in 16 seconds, while if you just run this subquery manually and use IN (1,2,3,…10) it completes in tiny fraction of the second.

Anyway EXPLAIN EXTENDED is very valuable addition to EXPLAIN for MySQL Performance troubleshooting.

Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.


Leave a Reply


Percona’s widely read Percona Database Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.