What interesting things will EXPLAIN EXTENDED tell us?

Most MySQL developers are familiar with the EXPLAIN team, but much less people know about the EXPLAIN EXTENDED command, which appeared in MySQL 4.1, and even less know how to use it.

EXPLAIN EXTENDED is able to show what exactly the MySQL optimizer does with your query. It may not be obvious to the developer how much the request written by him can differ from the one that will actually be executed by the server. This process is called the query rewrite mechanism, and it is part of any good SQL optimizer. The EXPLAIN EXTENDED command adds additional warnings to the output of the EXPLAIN command, including the rewritten SQL query.


First, let's create three empty tables. Now it is important for us that the tables are empty, because The MySQL optimizer works especially with empty tables (and sometimes with tables containing only one row) than with tables containing more than one row.

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)

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 may notice some strange things here in the output of the EXPLAIN command. First, it does not list any tables. Looking at the Extra column, you can see that MySQL mentioned the word 'const' there. The tables 'const' are just those tables that contain 0 or 1 row, or tables whose all parts of the PRIMARY or UNIQUE keys fully satisfy the parameters in WHERE. If a table of type 'const' does not contain rows and is not used in OUTER JOIN, then MySQL will immediately return an empty result, because there can be no intersection of tables satisfying the query. MySQL achieves this by adding WHERE - WHERE 0 instead of the parameters.

Let's see what happens if we add one (1) to each of the tables:
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)

As you can see, now the tables appeared in the EXPLAIN results, however, pay attention to the fact that the table type is specified as 'system'. A table of this type is a type of table of type 'const' containing only one row. The contents of this table are read completely before the start of the query, so MySQL can compare the values ​​from the table with the given parameters as constants even before the execution plan is formed. In addition, MySQL replaced all parameters in WHERE with WHERE 1, because he knows that all tables used in the query contain the same values. If this were not so, then, as in the previous case, he would add WHERE 0.

Finally, let's add some more data to the tables and test the query:
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)

In the type column you can now see the value ALL, which means that MySQL reads the entire table. This is because there are no indexes in the tables.

There is something else interesting that you should pay attention to earlier: You probably already noticed that all the tables listed with a comma are automatically joined by the MySQL JOIN optimizer. Thus, disputes about what works faster and more optimally - enumerating tables with a comma or JOIN - become meaningless, because it is essentially the same thing.

And the last one. Let's see what EXPLAIN EXTENDED can tell us about the behavior of the MySQL optimizer when using view, which uses the MERGE algorithm:
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 interesting and important is written here in WHERE. As you can see, the request described when creating the view was rewritten according to the parameters that I specified in the WHERE of the request that accesses it.

From translator

Interesting facts from the comments on the article:

• EXPLAIN EXTENDED is conveniently used to determine why the index you created is not used. In particular, this becomes apparent when the encoding does not match somewhere in the fields. In this case, the output will look something like this:
  SELECT … where (`b`.`t1`.`a` = convert(`b`.`t2`.`b` using utf8))
• Using the EXPLAIN EXTENDED command on production servers should be used with great care, as described bugs dropping the server ( proof link )