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
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"...)
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.
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. +
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 +
Dependency created: WL#218 now depends on WL#51
Observers changed: Sergei
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
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
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
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
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
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
