MySQL Bugs: #14745: EXPLAIN UPDATE statements

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

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.)

Yes, it's in the TODO

And other statements like delete, insert and truncate.
Maybe even ddl statements like alter table.

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 --

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/

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.

Duplicate of bug #35355?

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.

This is a duplicate of WL#4897.

And duplicate of BUG#35355.

It's a duplicate of a worklog item that the public can't see. That stinks.