Bug #14745 EXPLAIN UPDATE statements
Submitted: 8 Nov 2005 14:36 Modified: 22 Nov 2010 12:59
Reporter: Dave Pullin (Basic Quality Contributor) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:future OS:Any (all)
Assigned to: Geir Høydalsvik CPU Architecture:Any
Tags: qc

[8 Nov 2005 14:36] Dave Pullin
Description:
Could EXPLAIN be made to work on UPDATE statements?

Complex UPDATEs (eg with JOIN etc) involve as much performance optimization as complex SELECTs (both manually constructing access paths, and, presumably, within the MySQL opimizer).

It is possible to rewrite, by hand, an UPDATE statement into a SELECT statement that I might guess uses the same access strategy, but it is just a guess. If MySQL did it automatically then the result would be reliable.

How to repeat:
EXPLAIN UPDATE temp1 join temp2 using(x) set y=z;

would yield a result set similar to the result of
EXPLAIN SELECT * from temp1 join temp2 using(x) ;

(except that if the optimizer would use different paths for the update than the select it would correctly report that.)
[15 Nov 2005 11:55] Sergei Golubchik
Yes, it's in the TODO
[27 Jun 2006 20:01] Andre Timmer
And other statements like delete, insert and truncate.
Maybe even ddl statements like alter table.
[29 Jun 2007 20:37] J Jorgenson
Its been 2 years since this was requested: EXPLAIN UPDATE Statments.
Any way to push this to a higher priority?

This feature would help simplify some usability of the database, 
and helping the DBA.

-- JJ --
[16 Jun 2008 10:33] Yann-Gael GAUTHERON
I'm having lots of difficulties with a very big UPDATE query and i need to improve its performance...

Please, put the EXPLAIN UPDATE in an higher priority !

Here's the query, if anyone can help me in any way :

UPDATE `ship`,`ship_roadmap`
                        SET
                                `ship`.`x`=IF(@Cx_reached:=
                                        (
                                                        IF(
                                                                `ship_roadmap`.`x` > `ship`.`x`
                                                                ,`ship`.`x`+(@Cx:=(@AC:=`ship`.`speed`*TIMESTAMPDIFF(SECOND,`ship`.`date_position`,NOW())/10) * (`ship_roadmap`.`x`-`ship`.`x`) / (@AB:=SQRT(POWER(`ship`.`x`-`ship_roadmap`.`x`,2)+POWER(`ship`.`y`-`ship_roadmap`.`y`,2)))) > `ship_roadmap`.`x`
                                                                ,`ship`.`x`+(@Cx:=(@AC:=`ship`.`speed`*TIMESTAMPDIFF(SECOND,`ship`.`date_position`,NOW())/10) * (`ship_roadmap`.`x`-`ship`.`x`) / (@AB:=SQRT(POWER(`ship`.`x`-`ship_roadmap`.`x`,2)+POWER(`ship`.`y`-`ship_roadmap`.`y`,2)))) < `ship_roadmap`.`x`
                                                        )
                                                OR
                                                        `ship`.`x`=`ship_roadmap`.`x`
                                        )
                                        ,`ship_roadmap`.`x`
                                        ,`ship`.`x`+@Cx
                                )
                                , `ship`.`y`=IF(@Cy_reached:=
                                        (
                                                        `ship`.`y`=`ship_roadmap`.`y`
                                                OR
                                                        IF(
                                                                `ship_roadmap`.`y` > `ship`.`y`
                                                                ,`ship`.`y`+(@Cy:=@AC*(`ship_roadmap`.`y`-`ship`.`y`)/@AB) > `ship_roadmap`.`y`
                                                                ,`ship`.`y`+(@Cy:=@AC*(`ship_roadmap`.`y`-`ship`.`y`)/@AB) < `ship_roadmap`.`y`
                                                        )
                                        )
                                        ,`ship_roadmap`.`y`
                                        ,`ship`.`y`+@Cy
                                )
                                , `ship`.date_position=                 NOW()
                                , `ship`.id_station=                            IF(@Cx_reached AND @Cy_reached
                                                                                                                ,`ship_roadmap`.`id_station`
                                                                                                                ,null)
                                , `ship`.id_ship_roadmap_done = IF(@Cx_reached AND @Cy_reached
                                                                                                                ,`ship_roadmap`.`id_ship_roadmap`
                                                                                                                ,null)
                        WHERE
                                `ship`.`id_galaxie`=`ship_roadmap`.`id_galaxie`
                                AND `ship`.`id_systeme`=`ship_roadmap`.`id_systeme`
                                AND `ship`.`id_astre`=`ship_roadmap`.`id_astre`
                                AND `ship_roadmap`.`id_ship_roadmap`=(SELECT s2.`id_ship_roadmap` FROM `ship_roadmap` s2 WHERE s2.`id_ship`=`ship`.id_ship ORDER BY s2.`offset` ASC, s2.`id_ship_roadmap` ASC LIMIT 1);

The goal is to modify coordinates of space ships in a 2D environment... If i can't improve this query, i won't be able to add an other dimension to have a real 3D space and the only way is to understand how the variables are calculated and what is the evaluation order.

(Index improvment are already done)

Thanks for reading, 
Yann Gaël GAUTHERON
http://www.adsolaris.eu/
[16 Jun 2008 11:46] Dave Pullin
It would be helpful if a MySQL developer could confirm that the "obvious" re-write of a UPDATE into a SELECT would yield the the EXPLAIN results that would give same access strategy.

BY "obvious" I means
  UPDATE s1,s2 Set this=that 

becomes
  SELECT that from s1,s2

I'm pretty sure it would shed light on Yann's performance problem.
[11 Nov 2008 19:31] Valeriy Kravchuk
Duplicate of bug #35355?
[5 Jan 2009 16:59] Kevin Benton
I agree - they look like dupes, but this one doesn't ask for explain delete.  I agree that this is a necessary RFE.  One somewhat hidden benefit of explain is asking MySQL to syntax check a statement without actually executing the statement.  This can be beneficial when trying new methods of building statements without asking MySQL to make any changes to the data.
[22 Nov 2010 12:59] Manyi Lu
This is a duplicate of WL#4897.
[22 Nov 2010 13:45] Manyi Lu
And duplicate of BUG#35355.
[29 Nov 2010 15:44] Kevin Benton
It's a duplicate of a worklog item that the public can't see. That stinks.