Now MySQL supports EXPLAIN only for SELECT queries, which of course are usually 
the most complex ones. 
 
So if one uses DELETE/UPDATE etc queries one needs to rewrite them to SELECT 
first before running EXPLAIN. 
 
We need to add support for EXPLAIN for other type of queries to avoid this 
need. 

• WL#1488: Implement Smart Explain

Syntax

------



EXPLAIN [ EXTENDED | PARTITIONS ] statement;



Where 'statement' might be one, some, or all of the statements

that the MySQL Reference Manual lists as "Data Manipulation Statements":

CALL, DELETE, DO, HANDLER, INSERT, LOAD DATA INFILE, LOAD XML,

REPLACE, SELECT [possibly with INTO], TRUNCATE, UPDATE.

A priority can be the statements that optionally contain WHERE clauses:

DELETE, UPDATE, INSERT ... SELECT, SELECT [INTO], and CREATE ... SELECT.



There is no intent to support EXPLAIN for table-maintenance,

data-description-language, transaction-control, backup-restore, utility,

or non-preparable statements,



The user must have SELECT privilege for any referenced tables,

and EXECUTE privilege for any referenced functions, in 'statement'.

Or, the user must have the same privileges as would be required

to actually execute 'statement'.



The result will be comparable to what MySQL does for EXPLAIN SELECT,

as described in the MySQL Reference Manual section

"Optimizing Queries with EXPLAIN"

http://dev.mysql.com/doc/refman/6.0/en/using-explain.html

except that the word SELECT may be replaced with DELETE, UPDATE, etc.



Other DBMSs

-----------



DB2 supports EXPLAIN for SELECT, INSERT, UPDATE, DELETE, SELECT INTO, 

and VALUES [INTO] (VALUES is similar to our SELECT with no FROM clause). 

If we decide to support EXPLAIN for SELECT INTO, see BUG#15463. 

 

Oracle supports EXPLAIN PLAN for SELECT, INSERT, UPDATE, DELETE, ]

CREATE TABLE, CREATE INDEX, and ALTER INDEX ... REBUILD. 



Transforming to SELECT

----------------------



One way to handle EXPLAIN UPDATE is to transform it automatically 

into a SELECT, taking all columns in the SET clause and putting 

them in the select list, and ignoring clauses specific to UPDATE. 

For example: 

EXPLAIN UPDATE LOW_PRIORITY t SET column1=column2 WHERE column3=7; 

becomes 

EXPLAIN SELECT column1, column2 FROM t WHERE column3=7; 

Then the required privilege continues to be SELECT (not UPDATE), 

and the output continues to contain a column named 'select_type', 

and there is no information about what UPDATE does after retrieving. 

 

Requirements for foreign keys

-----------------------------



[ This section was moved from the WL#148 LLD. ]



In an early edition of the LLD for WL#148 "Foreign Keys ...",

there was a suggestion that EXPLAIN UPDATE would show effects

of cascades:



"

EXPLAIN. It may not always be obvious at a glance what

MySQL will do with a data-change statement on a table

that might reference or be referenced by foreign keys, so

there is an enhancement to the EXPLAIN statement. Thus:

EXPLAIN [ UPDATE | INSERT | DELETE | REPLACE statement ];

When you say "EXPLAIN UPDATE t1 SET a=5", you will see:

what tables are involved if there is a foreign key, and

what flags were set due to storage engine capabilities

(for example "non-transactional").

"

Also, EXPLAIN would show what flags were set by storage

engines for each affected table.

For example:

"

mysql> EXPLAIN UPDATE UPDATE t1 SET s1 = 2;

"... CASCADING EFFECT: update of t1 causes SET DEFAULT of t2.

 ... CASCADING EFFECT: SET DEFAULT of t2 causes CASCADE of t3.

 These tables may be affected: t1 (Solid), t2 (InnoDB), t3 (Falcon).

 The Solid storage engine says: beware, I am pessimistic.

"



See dev-runtime thread Re: Coherent LLD for WL#148 "Foreign keys"

[mysql intranet address] /secure/mailarchive/mail.php?folder=214&mail=512



But in the third Heidelberg meeting on foreign keys

[mysql intranet address] /secure/mailarchive/mail.php?folder=214&mail=564

there was a decision

"

1) WL#706 - explain update - should be amended to show the tables affected by

   foreign keys



   A separate new worklog task is to be created unless WL#706

   allows for extension. Otherwise the new worklog task will have a

   dependency on WL#706.

"



Therefore that requirement was removed from WL#148 LLD and added here.



EXPLAIN with foreign keys and multiple storage engines

------------------------------------------------------



[ This section was moved from the WL#148 LLD. ]



During the revision of the LLD, it became apparent that the rules

can be complex due to flag settings and the weakest-link-in-chain

approach for cross-storage-engine foreign keys. So we should tell

users what a statement will do. The EXPLAIN statement seemed most

appropriate for this purpose, although it's not usually legal for

data-change statements, and the information won't be an optimizer

output. This will require a change in the HLS.



explain()

  if (UPDATE or INSERT or DELETE)

    if (@@foreign_key_for_all_engines==0)

      output("any foreign keys are handled by storage engines")

    else

      fk_prepare();

      output("these conditions apply ...");

      for (each fk_statement_flags setting that is ON)

        output(name of statement_flag)

      output("these constraints will be used ...")

      for (each constraint name)

        output(name of constraint)



Note added by Martin Skold 2005-06-21

------------------------------------- 



As of 5.0.4, EXPLAIN [EXTENDED] also returns information 

about any pushed conditions to storage engines (enabled by  

set engine_condition_pushdown=on;), currently only supported by ndbcluster. 

Condition pushdown is also done for UPDATE/DELETE, so this work has to include 

verifying EXPLAIN works in combination with that as well. 



Customer Requirement

--------------------



There was a note saying "This feature is required by [customer name]".

The note is removed. See progress report dated 2009-07-30.



WL#4897 Add EXPLAIN UPDATE/DELETE

---------------------------------



Gleb Shchepa explains why he made a new worklog task:

"

I think WL#706 is slightly different: it is for [customer name] and about

[customer name].



As far as I see, WL#706 is suspended till engine-independent Foreign

Keys implementation.

I.e. new WL#4897 may be an early subtask for this WL that doesn't care

about FKs.



Moreover, the main idea of WL#706 is to transform EXPLAIN UPDATE/DELETE

directly into EXPLAIN SELECT: IMO it is not acceptable for the current

DELETE/UPDATE implementation.

"



Peter Gulutzan believes that WL#4897 should be incorporated into WL#706,

or cancelled.



References

----------



BUG#14745 EXPLAIN UPDATE statements

BUG#35355 Support explain for update and delete statements

You must be logged in to tag this worklog