Bug #35355 Support explain for update and delete statements
Submitted: 17 Mar 2008 23:14 Modified: 16 Oct 2012 4:40
Reporter: Mark Callaghan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.0 OS:Any
Assigned to: Geir Høydalsvik CPU Architecture:Any
Tags: delete, explain, UPDATE

[17 Mar 2008 23:14] Mark Callaghan 
Description:
Given that delete statements are not optimized using the same code as select statements (see http://bugs.mysql.com/bug.php?id=30584), it would help to be able to explain delete and update statements, but 'explain delete ...' and 'explain update ...' fail.

How to repeat:
mysql> create table j (i int);
Query OK, 0 rows affected (0.00 sec)

mysql> explain update j set i=0 where i=1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update j set i=0 where i=1' at line 1
[19 Mar 2008 18:43] Susanne Ebrecht 
Many thanks for reporting a feature request.
EXPLAIN isn't standard SQL but you can do EXPLAIN DELETE ... and EXPLAIN UPDATE at all other RDBMS that I know and that have EXPLAIN implemented.
[3 Jun 2008 20:32] James Shaw 
I'd suggest this is a duplicate of #14745
[3 Jun 2008 21:09] Mark Callaghan 
Yes, it is if the other one is expanded to include replace, delete, insert as select and create table as select.
[8 Mar 2010 13:41] Manyi Lu 
This is a feature request, see worklog WL#706, WL#4897.
[8 Mar 2010 21:07] Mark Callaghan 
Worklog 4897 is private.

What is the ETA for delivering this? I prefer to avoid production emergencies from insert/update/delete statements that have bad plans which would be obvious if EXPLAIN were supported.
[8 Apr 2010 12:09] Sveta Smirnova 
See also bug #52686
[22 Nov 2010 15:20] Tyler MacDonald 
Manyi, can you please make WL#4897 public?
[15 Oct 2012 14:57] Erlend Dahl 
Implemented in 5.6.3