MySQL 5.7 EXPLAIN EXTENDED statement description
EXPLAIN EXTENDED displays an additional filtered field compared to the EXPLAIN command. This field indicates the percentage of rows in the table filtered by the table's criteria. In addition, after executing the EXPLAIN EXTENDED command, you can use the SHOW WARNINGS statement to view additional statement information. In MySQL 5.7.3, the EXTENDED parameter is automatically taken with the explain command.
Mysql > EXPLAIN EXTENDED SELECT t1.a, t1.an IN (SELECT t2.a FROM T2) FROM T1
+-- +
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-- +
| | 1 | PRIMARY | T1 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
| | 2 | SUBQUERY | T2 | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
+-- +
2 rows in set, 2 warnings (0.00 sec)
Mysql > SHOW WARNINGS\ G
* * 1. Row *
Level: Warning
Code: 1681
Message: 'EXTENDED' is deprecated and will be removed in a future release.
* 2. Row * *
Level: Note
Code: 1003
Message: / * select#1 * / select `fire`.`t1`.`a`AS `a`, (`fire`.`t1`.`a`, `fire`.`t1`.`a`in (/ * select#2 * / select `fire`.`t2`.`a`from `fire`.`t2` where 1 having 1), (`fire`.`t1`.`a`in on where ((`fire`.`t1`.`a` = `materialized- subquery`.`a`) AS `t1.an IN (SELECT t2.a FROM T2) `from `fire.`t1`)
2 rows in set (0.00 sec)
Mysql > EXPLAIN SELECT t1.a, t1.an IN (SELECT t2.a FROM T2) FROM T1
+-- +
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-- +
| | 1 | PRIMARY | T1 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
| | 2 | SUBQUERY | T2 | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
+-- +
2 rows in set, 1 warning (0.00 sec)
Mysql > SHOW WARNINGS\ G
* * 1. Row *
Level: Note
Code: 1003
Message: / * select#1 * / select `fire`.`t1`.`a`AS `a`, (`fire`.`t1`.`a`, `fire`.`t1`.`a`in (/ * select#2 * / select `fire`.`t2`.`a`from `fire`.`t2` where 1 having 1), (`fire`.`t1`.`a`in on where ((`fire`.`t1`.`a` = `materialized- subquery`.`a`) AS `t1.an IN (SELECT t2.a FROM T2) `from `fire.`t1`)
1 row in set (0.00 sec)
