What interesting will tell us EXPLAIN EXTENDED?
Most MySQL developers are familiar with the EXPLAIN command, but much less people know about the EXPLAIN EXTENDED command, which appeared in MySQL 4.1, and even less able to use it.
EXPLAIN EXTENDED can show what exactly the MySQL optimizer does to your query. For the developer, it may not be at all obvious how much the request written by him can differ from the one that is actually executed by the server. This process is called a query rewrite mechanism, and it is part of any good SQL optimizer. The EXPLAIN EXTENDED command adds additional warnings (warnings) to the output of the EXPLAIN command, including a rewritten SQL query.
First, let's create three empty tables. Now it is important for us that the tables be exactly empty, because MySQL's optimizer works in a special way with empty tables (and sometimes with tables containing only one row) than with tables containing more than one row.
')
Here you may notice a few strange things in the output of the EXPLAIN command. The first is that no tables are listed in it. Looking at the Extra column you can see that MySQL has mentioned the word 'const' there. The 'const' tables are just those tables that contain 0 or 1 row, or tables, all parts of which PRIMARY- or UNIQUE-keys fully satisfy the parameters in the WHERE. If the table of the type 'const' does not contain rows and is not used in the OUTER JOIN, then MySQL will immediately return an empty result, since There can be no intersection of tables that satisfy the query. MySQL does this by adding instead of WHERE - WHERE 0 parameters.
Let's see what happens if we add one (1) to each of the tables:
As you can see, now the tables appeared in the results of the EXPLAIN execution, but note that the table type is specified as 'system'. A table of this type is a variation of a 'const' type table containing only one row. The contents of this table are read completely before the query is executed, so that MySQL can compare the values from the table with the specified parameters as constants before the execution plan is formed. In addition, MySQL replaced all parameters in WHERE with WHERE 1, since 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:
In the type column, you can now see the value ALL, which means that MySQL is reading the entire table. This is because there are no indexes in the tables.
There is one more interesting thing that you should have paid attention to before: you probably already noticed that all the tables, separated by a comma, are automatically merged by the MySQL optimizer JOINs. Thus, disputes about what works faster and more optimally - listing tables separated by commas or JOIN - become meaningless, because it is essentially the same thing.
And the last. Let's see what EXPLAIN EXTENDED can tell us about the behavior of the MySQL optimizer when using a view that uses the MERGE algorithm:
The most interesting and important here is written in WHERE. As you can see, the query described when creating the view was rewritten according to the parameters I specified in the WHERE query referring to it.
Interesting facts from the comments on the article:
EXPLAIN EXTENDED can show what exactly the MySQL optimizer does to your query. For the developer, it may not be at all obvious how much the request written by him can differ from the one that is actually executed by the server. This process is called a query rewrite mechanism, and it is part of any good SQL optimizer. The EXPLAIN EXTENDED command adds additional warnings (warnings) to the output of the EXPLAIN command, including a rewritten SQL query.
First, let's create three empty tables. Now it is important for us that the tables be exactly empty, because MySQL's optimizer works in a special way 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)Here you may notice a few strange things in the output of the EXPLAIN command. The first is that no tables are listed in it. Looking at the Extra column you can see that MySQL has mentioned the word 'const' there. The 'const' tables are just those tables that contain 0 or 1 row, or tables, all parts of which PRIMARY- or UNIQUE-keys fully satisfy the parameters in the WHERE. If the table of the type 'const' does not contain rows and is not used in the OUTER JOIN, then MySQL will immediately return an empty result, since There can be no intersection of tables that satisfy the query. MySQL does this by adding instead of WHERE - WHERE 0 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 results of the EXPLAIN execution, but note that the table type is specified as 'system'. A table of this type is a variation of a 'const' type table containing only one row. The contents of this table are read completely before the query is executed, so that MySQL can compare the values from the table with the specified parameters as constants before the execution plan is formed. In addition, MySQL replaced all parameters in WHERE with WHERE 1, since 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 is reading the entire table. This is because there are no indexes in the tables.
There is one more interesting thing that you should have paid attention to before: you probably already noticed that all the tables, separated by a comma, are automatically merged by the MySQL optimizer JOINs. Thus, disputes about what works faster and more optimally - listing tables separated by commas or JOIN - become meaningless, because it is essentially the same thing.
And the last. Let's see what EXPLAIN EXTENDED can tell us about the behavior of the MySQL optimizer when using a view that 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 here is written in WHERE. As you can see, the query described when creating the view was rewritten according to the parameters I specified in the WHERE query referring to it.
From translator
Interesting facts from the comments on the article:
- EXPLAIN EXTENDED is convenient to use to determine why the index you created is not used. In particular, this becomes obvious when somewhere in the fields the encoding does not match. In this case, the output will look something like this:
SELECT … where (`b`.`t1`.`a` = convert(`b`.`t2`.`b` using utf8)) - Use the command EXPLAIN EXTENDED command on production servers with great caution, because Bugs that drop a server are described ( proof link )
Source: https://habr.com/ru/post/98904/
All Articles