While many people are familiar with the MySQL EXPLAIN command, fewer people are familiar with “explain extended” which was added in MySQL 4.1

EXPLAIN EXTENDED

…can show you what the MySQL optimizer does to your query. You might not know this, but MySQL can dramatically change your query before it actually executes it. This process is called query rewriting, and it is a part of any good SQL optimizer. EXPLAIN EXTENDED adds a warning message to the EXPLAIN output which displays additional information, including the rewritten query.

To take a look at EXPLAIN EXTENDED, I’ll start with three empty tables. It is important to note that the tables are empty because the MySQL optimizer treats empty tables (and incidentally, tables with only one row) differently than tables which contain more than one row. More about that in a bit.

You might notice a few odd things about this EXPLAIN. First, there are no tables listed. Taking a look at the Extra column we see that MySQL mentions ‘const’ tables. A ‘const’ table is a table that contains 0 or 1 rows, or a table on which all parts of a primary key or unique key lookup are satisfied in the where clause. If a ‘const’ table contains no rows, and it is not used in an OUTER JOIN, then MySQL can immediately return an empty set because it infers that there is no way that rows could be returned. MySQL does this by adding the WHERE clause in the query with ‘where 0’.

Let’s now look at what happens after the value (1) is inserted into each of the tables. Each table contains only a single row, and the value in each table is 1.

You should now notice that the tables are actually listed in the EXPLAIN output, but also notice that the type field is set to ‘system’. A ‘system’ table is a special case of ‘const’ table which is used when a table contains only one row. The contents of these tables are read before the query begins executing. Because of this, MySQL can compare the constant values before completely formulating the plan. You will notice the MySQL replaces the WHERE clause with ‘where 1’ because it knows that all the const tables contain equal values. If they did not, the above plan with the ‘where 0’ would be generated.

Finally, lets insert a few more rows and test the plan:

The type field has now changed to ALL, which means that the entire table will be read. This is because these tables contain no indexes.

There is another interesting thing, and I probably should have mentioned it before. You will notice that the query that I explained used the comma syntax, but when MySQL rewrote the query it switched it to use the JOIN keyword. This should put to rest any debate as to any perceived performance difference between comma join and ANSI JOIN syntax. They are intrinsically the same.

Last, EXPLAIN EXTENDED can show you information about the query rewrites that MySQL makes when accessing views which use the MERGE algorithm.

For example:

The most important thing to notice is the WHERE clause. You will see that the SELECT statement used in the view has been modified to include the WHERE clause that I used when accessing the view.

7 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Shane Bester

My favourite use of explain extended is to check if optimizer doesn’t use indexes due to conversions of charsets. For example it’ll show something like this:

SELECT … where (b.t1.a = convert(b.t2.b using utf8))

Sometimes when tables are temporary or created as result of some queries, it’s
not always obvious what their schemas are 🙂

0
Bill

I’ve been using MySQL for a long time, before 4.1, I’m a little embarrassed I never knew this existed.

0
Justin Swanhart

Hi Shane,

Thanks for the tip.

0
Matthew Rudy Jacobs

I remember when “EXPLAIN EXTENDED” crashed our DB
:s

0
Shane Bester

Oh yes, I forgot to mention explain extended is very unstable and will crash server for certain queries.. put [ site:bugs.mysql.com “explain extended” crash ] into google for proof 🙂 be careful!

0
daveyhook

Hi Peter,
It seems that a ‘const’ table contains no rows may be used in an OUTER JOIN in newer version.

mysql> EXPLAIN extended SELECT j1.c1 FROM j1, j2, j3 WHERE j1.c1 = j2.c1 AND j3.c1 = j1.c1;
+—-+————-+——-+——+—————+——+———+——+——+—————————————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+——+—————————————————–+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+—-+————-+——-+——+—————+——+———+——+——+—————————————————–+
1 row in set, 1 warning (0.01 sec)

mysql> show warnings;
+——-+——+—————————————————————————————————————————————————————————-+
| Level | Code | Message |
+——-+——+—————————————————————————————————————————————————————————-+
| Note | 1003 | select test.j1.c1 AS c1 from test.j1 join test.j2 join test.j3 where ((test.j2.c1 = test.j1.c1) and (test.j3.c1 = test.j1.c1)) |
+——-+——+—————————————————————————————————————————————————————————-+
1 row in set (0.00 sec)

mysql> select version();
+—————-+
| version() |
+—————-+
| 5.0.26-max-log |
+—————-+
1 row in set (0.00 sec)

0
Shantanu Oak

I use explain extended to know the table name that the column belongs. I lot of programmers do not mention the table alias before the column name in the query, if the column name is unique. I find it easy to use extended to know the table name.

0