Optimizer: new EXPLAIN for data-modifiers

One of the new features of MySQL 5.6.3 is EXPLAIN command for data-modifying statements.
As it's obvious from its name, it outputs QEP (query execution plan) for INSERT, REPLACE, UPDATE and DELETE like old good one does for SELECT:

example:

mysql> EXPLAIN UPDATE t1 SET b = 'b' WHERE a > 2;
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | t1    | range | a,a_2         | a    | 16      | NULL |    2 | Using where; Using temporary |
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)

All kinds of MySQL syntax are supported:

INSERT from SELECT:

mysql> EXPLAIN INSERT INTO t1 SELECT * FROM t2 WHERE a IN (1, 3, 5);
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | t2    | range | t2i1          | t2i1 | 4       | NULL |    3 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

EXPLAIN for INSERT ... VALUES works as a special case of the INSERT ... SELECT VALUES FROM DUAL:

trivial EXPLAIN INSERT ... VALUES:

mysql> EXPLAIN INSERT INTO t1 VALUES (1, 2, 3);
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

mysql> EXPLAIN INSERT INTO t1 SELECT 1, 2, 3 FROM DUAL;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

multi-table UPDATE:

mysql> EXPLAIN UPDATE t1, t2 SET t1.b = t2.b WHERE t1.a = t2.a;
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref       | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------+
|  1 | SIMPLE      | t1    | ALL  | PRIMARY       | NULL | NULL    | NULL      | 3072 |       |
|  1 | SIMPLE      | t2    | ref  | t2i1          | t2i1 | 4       | test.t1.a |    1 |       |
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------+
2 rows in set (0.00 sec)

multi-table DELETE:

mysql> EXPLAIN DELETE FROM t1 USING t1 JOIN t2 ON t1.a = t2.a;
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref       | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------------+
|  1 | SIMPLE      | t1    | ALL  | PRIMARY       | NULL | NULL    | NULL      | 3072 |             |
|  1 | SIMPLE      | t2    | ref  | t2i1          | t2i1 | 4       | test.t1.a |    1 | Using index |
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------------+
2 rows in set (0.00 sec)

For single-table UPDATE and DELETE it may be interesting to explain subquery execution:

single-table UPDATE with a subquery:

mysql> EXPLAIN UPDATE t1 SET b = '12345' WHERE a IN (SELECT a FROM t2);
+----+--------------------+-------+----------------+---------------+------+---------+------+------+-------------+
| id | select_type        | table | type           | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------------+-------+----------------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY            | t1    | ALL            | PRIMARY       | NULL | NULL    | NULL | 3072 | Using where |
|  2 | DEPENDENT SUBQUERY | t2    | index_subquery | t2i1          | t2i1 | 4       | func |    1 | Using index |
+----+--------------------+-------+----------------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)

... or even:

single-table DELETE with subqueries:

mysql> EXPLAIN DELETE FROM t1 WHERE a IN (SELECT t2.a FROM t2 JOIN t3 WHERE t2.b = t3.b AND t3.a IN (SELECT a FROM t4));
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------- - - -
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------- - - -
|  1 | PRIMARY            | t1    | ALL  | PRIMARY       | NULL | NULL    | NULL | 3072 | Using where
|  2 | DEPENDENT SUBQUERY | t2    | ref  | t2i1          | t2i1 | 4       | func |    1 |
|  2 | DEPENDENT SUBQUERY | t3    | ALL  | NULL          | NULL | NULL    | NULL | 3072 | Using where;
|  3 | DEPENDENT SUBQUERY | t4    | ALL  | NULL          | NULL | NULL    | NULL | 3072 | Using where
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------- - - -
4 rows in set (0.00 sec)

Manuals are available here and here.