EXPLAIN EXTENDED shows what MySQL optimizer does to your query

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: