WL#6033: Add transformed query to EXPLAIN INSERT/UPDATE/DELETE
Affects: Server-8.0
—
Status: Complete
EXPLAIN EXTENDED SELECT statement outputs a notification(warning) with a transformed SELECT query text. However, EXPLAIN EXTENDED INSERT/REPLACE/UPDATE/DELETE commands lack of the same feature.
See BUG#62617: EXPLAIN EXTENDED WIHT DELETE, UPDATE ETC.
EXTENDED keyword has been removed in mysql-8.0 following WL#9678.
F1) There should be a warning for EXPLAIN insert/update/replace/delete statements.
F2) The content of the warning should reflect the query transformations that was done during optimization.
CREATE TABLE tbl1 (c1 INT NOT NULL, c2 varchar (64), PRIMARY KEY (c1)) ENGINE = InnoDB PARTITION BY RANGE (c1) SUBPARTITION BY HASH (c1) SUBPARTITIONS 2 (PARTITION p0 VALUES LESS THAN (0) (SUBPARTITION subp0, SUBPARTITION subp1), PARTITION p1 VALUES LESS THAN (100000) (SUBPARTITION subp6, SUBPARTITION subp7)); CREATE TABLE tbl2 (c1 int); CREATE TABLE tbl3 LIKE tbl2; INSERT INTO tbl1 VALUES (1, 'a'), (2, 'b'), (3, 'c'); INSERT INTO tbl2 SELECT c1 FROM tbl1; INSERT INTO tbl3 SELECT c1 FROM tbl2; Functional and Non-Functional Requirements EXTENDED keyword has been removed in mysql-8.0 following WL#9678. F1) DELETE F1.a) Single table DELETE: EXPLAIN DELETE LOW_PRIORITY QUICK IGNORE FROM tbl1 PARTITION (p1) WHERE c1 > 0 ORDER BY c2 LIMIT 10; +----+-------------+-------+-------------------+-------+---------------+---------+---------+-------+------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------------------+-------+---------------+---------+---------+-------+------+----------+-----------------------------+ | 1 | DELETE | tbl1 | p1_subp6,p1_subp7 | range | PRIMARY | PRIMARY | 4 | const | 3 | 100.00 | Using where; Using filesort | +----+-------------+-------+-------------------+-------+---------------+---------+---------+-------+------+----------+-----------------------------+ 1 row in set, 1 warning (0.01 sec) mysql> SHOW WARNINGS; +-------+------+------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | delete low_priority ignore from `test`.`tbl1` PARTITION (`p1`) where (`test`.`tbl1`.`c1` > 0) order by `test`.`tbl1`.`c2` limit 10 | +-------+------+------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) F1.b) Multi-table DELETE: EXPLAIN DELETE LOW_PRIORITY QUICK IGNORE tbl1 , tbl2 FROM tbl1 , tbl2, tbl3 WHERE tbl1.c1 > 0; +----+-------------+-------+-------------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | DELETE | tbl1 | p1_subp6,p1_subp7 | ALL | PRIMARY | NULL | NULL | NULL | 3 | 100.00 | Using where | | 1 | DELETE | tbl2 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL | | 1 | SIMPLE | tbl3 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL | +----+-------------+-------+-------------------+------+---------------+------+---------+------+------+----------+-------------+ 3 rows in set, 1 warning (0.00 sec) mysql> SHOW WARNINGS; +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | delete low_priority ignore `test`.`tbl1`, `test`.`tbl2` from `test`.`tbl1` join `test`.`tbl2` join `test`.`tbl3` where (`test`.`tbl1`.`c1` > 0) | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) F2) UPDATE F2.a) Single table UPDATE: EXPLAIN UPDATE LOW_PRIORITY IGNORE tbl1 SET c1 = 20 WHERE c1 > 100; +----+-------------+-------+-------------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+ | 1 | UPDATE | tbl1 | p1_subp6,p1_subp7 | range | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using where; Using temporary | +----+-------------+-------+-------------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> SHOW WARNINGS; +-------+------+-------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------------------------------------------------+ | Note | 1003 | update low_priority ignore `test`.`tbl1` set `test`.`tbl1`.`c1` = 20 where (`test`.`tbl1`.`c1` > 100) | +-------+------+-------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) F2.b) Multi-table UPDATE: mysql> EXPLAIN UPDATE LOW_PRIORITY IGNORE tbl1 LEFT JOIN tbl2 ON tbl1.c1 = tbl2.c1 SET tbl1.c1 = 20 WHERE tbl1.c1 > 0; +----+-------------+-------+-------------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | UPDATE | tbl1 | p1_subp6,p1_subp7 | range | PRIMARY | PRIMARY | 4 | NULL | 3 | 100.00 | Using where | | 1 | SIMPLE | tbl2 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where | +----+-------------+-------+-------------------+-------+---------------+---------+---------+------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec) mysql> SHOW WARNINGS; +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | update low_priority ignore `test`.`tbl1``test`.`tbl1` left join `test`.`tbl2` on((`test`.`tbl2`.`c1` = `test`.`tbl1`.`c1`)) set `test`.`tbl1`.`c1` = 20 where (`test`.`tbl1`.`c1` > 0) | +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) F3) INSERT F3.a) INSERT-values EXPLAIN INSERT DELAYED IGNORE INTO tbl1 PARTITION(p0, p1) (c1, c2) VALUES (1, 'a'), (2, 'b'); +----+-------------+-------+-------------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | INSERT | tbl1 | p1_subp6,p1_subp7 | ALL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +----+-------------+-------+-------------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 2 warnings (0.00 sec) mysql> SHOW WARNINGS; +---------+------+-----------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------------------------+ | Warning | 3005 | INSERT DELAYED is no longer supported. The statement was converted to INSERT. | | Note | 1003 | insert into ignore `test`.`tbl1` PARTITION (`p0`,`p1`) (`test`.`tbl1`.`c1`,`test`.`tbl1`.`c2`) values (1,'a'),(2,'b') | +---------+------+-----------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) F3.b) INSERT VALUES.. ON DUPLICATE KEY .... EXPLAIN INSERT LOW_PRIORITY IGNORE INTO tbl1 PARTITION(p0, p1) (c1, c2) VALUES (1, 'a'), (2, 'b') ON DUPLICATE KEY UPDATE c2 = 'c' ; +----+-------------+-------+-------------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | INSERT | tbl1 | p1_subp6,p1_subp7 | ALL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +----+-------------+-------+-------------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> SHOW WARNINGS; +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | insert into low_priority ignore `test`.`tbl1` PARTITION (`p0`,`p1`) (`test`.`tbl1`.`c1`,`test`.`tbl1`.`c2`) values (1,'a'),(2,'b') on duplicate key update `test`.`tbl1`.`c2` = 'c' | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> F3.c) INSERT ... SELECT EXPLAIN INSERT DELAYED IGNORE INTO tbl1 PARTITION(p0, p1) (c1, c2) SELECT c1, 'a' FROM tbl2 ON DUPLICATE KEY UPDATE c2 = 'c' ; +----+-------------+-------+-------------------------------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------------------------------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | INSERT | tbl1 | p0_subp0,p0_subp1,p1_subp6,p1_subp7 | ALL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 1 | SIMPLE | tbl2 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL | +----+-------------+-------+-------------------------------------+------+---------------+------+---------+------+------+----------+-------+ 2 rows in set, 2 warnings (0.00 sec) mysql> SHOW WARNINGS; +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 3005 | INSERT DELAYED is no longer supported. The statement was converted to INSERT. | | Note | 1003 | insert into ignore `test`.`tbl1` PARTITION (`p0`,`p1`) (`test`.`tbl1`.`c1`,`test`.`tbl1`.`c2`) /* select#1 */ select `test`.`tbl2`.`c1` AS `c1`,'a' AS `a` from `test`.`tbl2` on duplicate key update `test`.`tbl1`.`c2` = 'c' | +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) f3.d) REPLACE ... VALUES EXPLAIN REPLACE LOW_PRIORITY INTO tbl1 PARTITION(p0, p1) (c1, c2) VALUES (1, 'a'), (2, 'b'); +----+-------------+-------+-------------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | REPLACE | tbl1 | p1_subp6,p1_subp7 | ALL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +----+-------------+-------+-------------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> SHOW WARNINGS; +-------+------+------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | replace low_priority into `test`.`tbl1` PARTITION (`p0`,`p1`) (`test`.`tbl1`.`c1`,`test`.`tbl1`.`c2`) values (1,'a'),(2,'b') | +-------+------+------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) F3.e) REPLACE ... SELECT EXPLAIN REPLACE DELAYED INTO tbl1 PARTITION(p0, p1) (c1, c2) SELECT c1, 'a' FROM tbl2; +----+-------------+-------+-------------------------------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------------------------------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | REPLACE | tbl1 | p0_subp0,p0_subp1,p1_subp6,p1_subp7 | ALL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 1 | SIMPLE | tbl2 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL | +----+-------------+-------+-------------------------------------+------+---------------+------+---------+------+------+----------+-------+ 2 rows in set, 2 warnings (0.00 sec) mysql> SHOW WARNINGS; +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 3005 | REPLACE DELAYED is no longer supported. The statement was converted to REPLACE. | | Note | 1003 | replace into `test`.`tbl1` PARTITION (`p0`,`p1`) (`test`.`tbl1`.`c1`,`test`.`tbl1`.`c2`) /* select#1 */ select `test`.`tbl2`.`c1` AS `c1`,'a' AS `a` from `test`.`tbl2` | +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.01 sec) F3.f) REPLACE ... SET (//Internally there is no distinction between SET and VALUES. So explain will also have no difference.) EXPLAIN REPLACE INTO tbl1 PARTITION(p0, p1) SET c1 = (SELECT c1 from tbl2); +----+-------------+-------+-------------------------------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------------------------------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | REPLACE | tbl1 | p0_subp0,p0_subp1,p1_subp6,p1_subp7 | ALL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 2 | SUBQUERY | tbl2 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL | +----+-------------+-------+-------------------------------------+------+---------------+------+---------+------+------+----------+-------+ 2 rows in set, 1 warning (3.52 sec) mysql> SHOW WARNINGS; +-------+------+-------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | replace into `test`.`tbl1` PARTITION (`p0`,`p1`) (`test`.`tbl1`.`c1`) values ((select `test`.`tbl2`.`c1` from `test`.`tbl2`)) | +-------+------+-------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.