This worklog has been replaced with mariadb.org/jira

This site is here for historical purposes only. Do not add or edit tasks here!

 
 
 

WorkLog Frontpage Log in / Register
High-Level Description | Task Dependencies | High-Level Specification | Low-Level Design | File Attachments | User Comments | Time Estimates | Funding and Votes | Progress Reports

 Add EXPLAIN for UPDATE/DELETE
Title
Task ID51
Queue
Version N/A
Status
PriorityN/A
Copies toSergei

Created byPsergey18 Aug 2009Done
Supervisor N/A  
Lead Architect    
Architecture Review  
Implementor  
Code Review  
QA  
Documentation  
 High-Level Description
Add support for EXPLAIN UPDATE/DELETE 

(we'd like support for all potentially long statements, e.g. for ALTER
TABLE, but this WL entry is limited to UPDATE/DELETE as they are most
often requested, and code that handles them is similar and distinct from
other statements).

Probably it will be in MySQL 5.6
 Task Dependencies
Others waiting for Task 51Task 51 is waiting forGraph
218 Better EXPLAIN
 
 High-Level Specification
User interface
--------------
EXPLAIN UPDATE ... and EXPLAIN DELETE ... statements will work and produce 
a tabular output similar to EXPLAIN SELECT.

Implementation
--------------
The primary challenge will be to change UPDATE/DELETE code to first produce 
action plan and then act on it (and not make decisions on the go as it
currently does).

What EXPLAIN will show
----------------------
* multi-table one will show the SELECT part as usual
* single-table statements will show an equivalent of access method.

Besides that, we want to know 
- if sorting will be used
- whether the UPDATE will occur on the fly or not.

(print or not "Using filesort"...)
 Low-Level Design
1. Backport of the the implementation from the mysql-5.6 code line.
   Estimate: 2.5 weeks (100 hrs)
   I got this estimate from comparison of the patch for
   ORDER BY with LIMIT that had been back-ported to 10.0-base
   with the mysql patch that added EXPLAIN for UPDATE/DELETE/INSERT.
   I took into account the fact that the latter was applied to the
   code that had been greatly changed from the mysql 5.5 code line.
   My personal attitude to the patch is extremely negative:
   the patch is overburdened with unneeded restructuring
   and unjustified new classes. The patch left the code for EXPLAIN
   interleaved with the code for SELECT and added interleaving of
   EXPLAIN for UPDATE/DELETE/INSERT for other code for this statements.

2. A new implementation that will:

   - Introduce a special class for abstract EXPLAIN objects that
     would allow an easy generation of of the EXPLAIN output in any
     format
   - Add methods that builds the EXPLAIN objects for each type of
     interesting statements by the chosen execution plans (plans
     can be changed at some fixed points during the execution)
   - Clean the current code for SELECT removing removing all code
     related to EXPLAIN.

   Basically the above are the basic ideas under the low level design
   that I could suggest.

   In the future this design will allow us to include EXPLAIN into
   the information schema and, as a direct consequence, will allow
   us to select whatever we are interested in from the EXPLAIN output.

   My estimate for the time needed for this implementation is about
   200 hrs (maybe even more).
   This is a lot, but it's only a fraction of the time that was
   in reality spent for SHOW EXPLAIN that DID NOT follow this
   design.

3. The steps to implement this are:
  - Design the class ( 1 day)
  - Implement the class (2 days)
  - Use it in DELETE to implement explain (1 day dev, 4 hour QA)
  - Use it in UPDATE
 - Change SELECT to also use the class.

 File Attachments
 NameTypeSizeByDate
 User Comments
 Time Estimates
NameHours WorkedLast Updated
Total0 
 Hrs WorkedProgressCurrentOriginal
Total000
 
 Funding and Votes
Votes: 0: 0%
 Make vote: Useless    Nice to have    Important    Very important    

Funding: 0 offers, total 0 Euro
 Progress Reports
(Monty - Fri, 05 Oct 2012, 15:34
    
Low Level Design modified.
--- /tmp/wklog.51.old.19754	2012-10-05 15:34:37.000000000 +0000
+++ /tmp/wklog.51.new.19754	2012-10-05 15:34:37.000000000 +0000
@@ -1,2 +1,46 @@
+1. Backport of the the implementation from the mysql-5.6 code line.
+   Estimate: 2.5 weeks (100 hrs)
+   I got this estimate from comparison of the patch for
+   ORDER BY with LIMIT that had been back-ported to 10.0-base
+   with the mysql patch that added EXPLAIN for UPDATE/DELETE/INSERT.
+   I took into account the fact that the latter was applied to the
+   code that had been greatly changed from the mysql 5.5 code line.
+   My personal attitude to the patch is extremely negative:
+   the patch is overburdened with unneeded restructuring
+   and unjustified new classes. The patch left the code for EXPLAIN
+   interleaved with the code for SELECT and added interleaving of
+   EXPLAIN for UPDATE/DELETE/INSERT for other code for this statements.
+
+2. A new implementation that will:
+
+   - Introduce a special class for abstract EXPLAIN objects that
+     would allow an easy generation of of the EXPLAIN output in any
+     format
+   - Add methods that builds the EXPLAIN objects for each type of
+     interesting statements by the chosen execution plans (plans
+     can be changed at some fixed points during the execution)
+   - Clean the current code for SELECT removing removing all code
+     related to EXPLAIN.
+
+   Basically the above are the basic ideas under the low level design
+   that I could suggest.
+
+   In the future this design will allow us to include EXPLAIN into
+   the information schema and, as a direct consequence, will allow
+   us to select whatever we are interested in from the EXPLAIN output.
+
+   My estimate for the time needed for this implementation is about
+   200 hrs (maybe even more).
+   This is a lot, but it's only a fraction of the time that was
+   in reality spent for SHOW EXPLAIN that DID NOT follow this
+   design.
+
+3. The steps to implement this are:
+  - Design the class ( 1 day)
+  - Implement the class (2 days)
+  - Use it in DELETE to implement explain (1 day dev, 4 hour QA)
+  - Use it in UPDATE
+ - Change SELECT to also use the class.
+
 
 

(Sergei - Tue, 19 Jul 2011, 06:47
    
High Level Description modified.
--- /tmp/wklog.51.old.28433	2011-07-19 06:47:22.000000000 +0000
+++ /tmp/wklog.51.new.28433	2011-07-19 06:47:22.000000000 +0000
@@ -5,3 +5,5 @@
 often requested, and code that handles them is similar and distinct from
 other statements).
 
+Probably it will be in MySQL 5.6
+

(Monty - Thu, 30 Jun 2011, 16:58
    
Dependency created: WL#218 now depends on WL#51

(Sergei - Mon, 04 Apr 2011, 11:15
    
Observers changed: Sergei

(Sergei - Thu, 01 Jul 2010, 05:48
    
Category updated.
--- /tmp/wklog.51.old.27899	2010-07-01 05:48:05.000000000 +0000
+++ /tmp/wklog.51.new.27899	2010-07-01 05:48:05.000000000 +0000
@@ -1 +1 @@
-Client-BackLog
+Server-RawIdeaBin

(Sergei - Thu, 01 Jul 2010, 05:48
    
Version updated.
--- /tmp/wklog.51.old.27899	2010-07-01 05:48:05.000000000 +0000
+++ /tmp/wklog.51.new.27899	2010-07-01 05:48:05.000000000 +0000
@@ -1 +1 @@
-Server-9.x
+9.x

(Guest - Sat, 02 Jan 2010, 21:34
    
Supervisor updated.
--- /tmp/wklog.51.old.21991	2010-01-02 21:34:01.000000000 +0200
+++ /tmp/wklog.51.new.21991	2010-01-02 21:34:01.000000000 +0200
@@ -1 +1 @@
-Bothorsen
+Monty

(Guest - Sat, 02 Jan 2010, 21:34
    
Privacy level updated.
--- /tmp/wklog.51.old.21991	2010-01-02 21:34:01.000000000 +0200
+++ /tmp/wklog.51.new.21991	2010-01-02 21:34:01.000000000 +0200
@@ -1 +1 @@
-y
+n

(Guest - Sat, 02 Jan 2010, 21:33
    
Version updated.
--- /tmp/wklog.51.old.21935	2010-01-02 19:33:25.000000000 +0000
+++ /tmp/wklog.51.new.21935	2010-01-02 19:33:25.000000000 +0000
@@ -1 +1 @@
-Benchmarks-3.0
+Server-9.x

(Guest - Sat, 02 Jan 2010, 21:33
    
Status updated.
--- /tmp/wklog.51.old.21935	2010-01-02 19:33:25.000000000 +0000
+++ /tmp/wklog.51.new.21935	2010-01-02 19:33:25.000000000 +0000
@@ -1 +1 @@
-Un-Assigned
+Assigned
-- View All Progress Notes (11 total) --


Report Generator:
 
Saved Reports:

WorkLog v4.0.0
  © 2010  Sergei Golubchik and Monty Program AB
  © 2004  Andrew Sweger <yDNA@perlocity.org> and Addnorya
  © 2003  Matt Wagner <matt@mysql.com> and MySQL AB