EXPLAIN EXTENDED

 

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.

Shell

1 2 3 4 5 6 7 8

mysql> create table j1 (c1 int); Query OK, 0 rows affected (0.16 sec)   create table j2 (c1 int); Query OK, 0 rows affected (0.11 sec)   mysql> create table j3 (c1 int); Query OK, 0 rows affected (0.10 sec)

Shell

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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.04 sec)   mysql> show warnings; +-------+------+-------------------------------------------------------------------------------+ | Level | Code | Message                                                                       | +-------+------+-------------------------------------------------------------------------------+ | Note  | 1003 | select '0' AS `c1` from `test`.`j1` join `test`.`j2` join `test`.`j3` where 0 | +-------+------+-------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

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.

Shell

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

mysql> insert into j1 values (1); insert into j2 select * from j1; insert into j3 select * from j2; Query OK, 1 row affected (0.00 sec)   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      | j1    | system | NULL          | NULL | NULL    | NULL |    1 |       | |  1 | SIMPLE      | j2    | system | NULL          | NULL | NULL    | NULL |    1 |       | |  1 | SIMPLE      | j3    | system | NULL          | NULL | NULL    | NULL |    1 |       | +----+-------------+-------+--------+---------------+------+---------+------+------+-------+ 3 rows in set, 1 warning (0.00 sec)   mysql> show warnings; +-------+------+-------------------------------------------------------------------------------+ | Level | Code | Message                                                                       | +-------+------+-------------------------------------------------------------------------------+ | Note  | 1003 | select '1' AS `c1` from `test`.`j1` join `test`.`j2` join `test`.`j3` where 1 | +-------+------+-------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

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:

Shell

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

mysql> insert into j1 values (1); insert into j2 select * from j1; insert into j3 select * from j2;   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      | j1    | ALL  | NULL          | NULL | NULL    | NULL |    2 |             | |  1 | SIMPLE      | j2    | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where | |  1 | SIMPLE      | j3    | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 3 rows in set, 1 warning (0.00 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)

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:

Shell

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

mysql> create view v1 as select * from j1; Query OK, 0 rows affected (0.10 sec)   mysql> explain extended select * from v1 where c1=1; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ |  1 | SIMPLE      | j1    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set, 1 warning (0.00 sec)   mysql> show warnings; +-------+------+-------------------------------------------------------------------------------+ | Level | Code | Message                                                                       | +-------+------+-------------------------------------------------------------------------------+ | Note  | 1003 | select `test`.`j1`.`c1` AS `c1` from `test`.`j1` where (`test`.`j1`.`c1` = 1) | +-------+------+-------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

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.