Extended EXPLAIN

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: