- developers - lists.mariadb.org

[Maria-developers] Updated (by Psergey): Make EXPLAIN show where subquery predicates are in the WHERE clause (111)
by worklog-noreply＠askmonty.org 29 Mar '10

29 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Make EXPLAIN show where subquery predicates are in the WHERE clause CREATION DATE..: Mon, 29 Mar 2010, 07:07 SUPERVISOR.....: Psergey IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 111 (http://askmonty.org/worklog/?tid=111) VERSION........: Server-5.3 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Mon, 29 Mar 2010, 08:16)=-=- High Level Description modified. --- /tmp/wklog.111.old.18136 2010-03-29 08:16:36.000000000 +0000 +++ /tmp/wklog.111.new.18136 2010-03-29 08:16:36.000000000 +0000 @@ -30,8 +30,15 @@ to expressions being too long for current EXPLAIN output format. A simplified solution would be to show "Subquery#n" in Extra column if the -clause in "Using Where" has a subquery. +clause in "Using Where" has a subquery. That is, if table's condition has +subquery expressions, Extra column will look like this: + Using where; Subquery#n1; Subquery#n2,... + +When table's condition has only subquery expression, we could omit printing +"Using where" (note that this case is quite rare now. the subquery must be +correlated, non-semi-join subquery predicate that is an AND-part of the WHERE. +such cases are in minority) [1] http://forge.mysql.com/wiki/Image:NewSubqueryOptimizationsIn6_0_UC2008.pdf -=-=(Guest - Mon, 29 Mar 2010, 07:09)=-=- Supervisor updated. --- /tmp/wklog.111.old.13259 2010-03-29 07:09:55.000000000 +0000 +++ /tmp/wklog.111.new.13259 2010-03-29 07:09:55.000000000 +0000 @@ -1 +1 @@ -Bothorsen +Psergey -=-=(Guest - Mon, 29 Mar 2010, 07:09)=-=- Category updated. --- /tmp/wklog.111.old.13259 2010-03-29 07:09:55.000000000 +0000 +++ /tmp/wklog.111.new.13259 2010-03-29 07:09:55.000000000 +0000 @@ -1 +1 @@ -Client-BackLog +Server-RawIdeaBin -=-=(Guest - Mon, 29 Mar 2010, 07:09)=-=- Version updated. --- /tmp/wklog.111.old.13259 2010-03-29 07:09:55.000000000 +0000 +++ /tmp/wklog.111.new.13259 2010-03-29 07:09:55.000000000 +0000 @@ -1 +1 @@ -Benchmarks-3.0 +9.x -=-=(Guest - Mon, 29 Mar 2010, 07:09)=-=- Version updated. --- /tmp/wklog.111.old.13259 2010-03-29 07:09:55.000000000 +0000 +++ /tmp/wklog.111.new.13259 2010-03-29 07:09:55.000000000 +0000 @@ -1 +1 @@ -9.x +Server-5.3 DESCRIPTION: Current EXPLAIN does not show where the subquery predicate is attached to. For example, see [1], slide#5 "Straightforward subquery evaluation (contd)", or look here: MariaDB [test]> explain select * from ot1, ot2 where ot1.a=ot2.a and (ot2.a in (select it1.b from it1) or ot1.b<3); +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+ | 1 | PRIMARY | ot1 | ALL | NULL | NULL | NULL | NULL | 10 | Using where | | 1 | PRIMARY | ot2 | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Using join buffer | | 2 | SUBQUERY | it1 | ALL | NULL | NULL | NULL | NULL | 20 | | +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+ 3 rows in set (0.01 sec) Here there are two "Using where", and it is not clear where the predicate is attached to. If one has sufficient knowledge, they could know that - "ot2.a in (select )" will be substituted for "ot1.a" (provided datatypes allow equality propagation) - For correlated subqueries, equality propagation will not affect outside references (so, if the subquery in the above example was correlated, it would have been attached to table ot2, not ot1). As one can see, the rules are quite complicated. The full solution would be to show expressions behind "Using Where", but that has additional complications due to expressions being too long for current EXPLAIN output format. A simplified solution would be to show "Subquery#n" in Extra column if the clause in "Using Where" has a subquery. That is, if table's condition has subquery expressions, Extra column will look like this: Using where; Subquery#n1; Subquery#n2,... When table's condition has only subquery expression, we could omit printing "Using where" (note that this case is quite rare now. the subquery must be correlated, non-semi-join subquery predicate that is an AND-part of the WHERE. such cases are in minority) [1] http://forge.mysql.com/wiki/Image:NewSubqueryOptimizationsIn6_0_UC2008.pdf ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Progress (by Knielsen): New replication APIs (107)
by worklog-noreply＠askmonty.org 29 Mar '10

29 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: New replication APIs CREATION DATE..: Mon, 15 Mar 2010, 13:55 SUPERVISOR.....: Knielsen IMPLEMENTOR....: Knielsen COPIES TO......: Sergei CATEGORY.......: Server-Sprint TASK ID........: 107 (http://askmonty.org/worklog/?tid=107) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 50 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Knielsen - Mon, 29 Mar 2010, 07:33)=-=- Research and design discussions: Galera, 2pc/XA, group commit, multi-engine transactions. Worked 14 hours and estimate 0 hours remain (original estimate increased by 14 hours). -=-=(Knielsen - Wed, 24 Mar 2010, 10:39)=-=- Design discussions Worked 11 hours and estimate 0 hours remain (original estimate increased by 11 hours). -=-=(Knielsen - Mon, 15 Mar 2010, 14:28)=-=- Research into the problem, and discussions on phone/mailing list Worked 25 hours and estimate 0 hours remain (original estimate increased by 25 hours). -=-=(Guest - Mon, 15 Mar 2010, 14:18)=-=- High-Level Specification modified. --- /tmp/wklog.107.old.9086 2010-03-15 14:18:18.000000000 +0000 +++ /tmp/wklog.107.new.9086 2010-03-15 14:18:18.000000000 +0000 @@ -1 +1,43 @@ +Current ideas/status after discussions on the mailing list: + + - Implement a set of plugin APIs and use them to move all of the existing + MySQL replication into a (set of) plugins. + + - Design the APIs so that they can support full MySQL replication, but also + so that they do not hardcode assumptions about how this replication + implementation is done, and so that they will be suitable for other types of + replication (Tungsten, Galera, parallel replication, ...). + + - APIs need to include the concept of a global transaction ID. Need to + determine the extent to which the semantics of such ID will be defined + by the API, and to which extend it will be defined by the plugin + implementations. + + - APIs should properly support reliable crash-recovery with decent + performance (eg. not require multiple mandatory fsync()s per commit, and + not make group commit impossible). + + - Would be nice if the API provided facilities for implementing good + consistency checking support (mainly checking master tables against slave + tables is hard here I think, but also applying wrong binlog data and + individual event checksums). + + +Steps to make this more concrete: + + - Investigate the current MySQL replication, and list all of the places where + a plugin implementation will need to connect/hook into the MySQL server. + * handler::{write,update,delete}_row() + * Statement execution + * Transaction start/commit + * Table open + * Query safe/not/safe for statement based replication + * Statement-based logging details (user variables, random seed, etc.) + * ... + + - Use this list to make an initial sketch of the set of APIs we need. + + - Use the list to determine the feasibility of this project and the level of + detail in the API needed to support a full replication implementation as a + plugin. -=-=(Serg - Mon, 15 Mar 2010, 14:13)=-=- Observers changed: Sergei DESCRIPTION: This is a top-level task for the project of designing a new set of replication APIs for MariaDB. This task is for the initial discussion of what to do and where to focus. The project is started in this email thread: https://lists.launchpad.net/maria-developers/msg01998.html HIGH-LEVEL SPECIFICATION: Current ideas/status after discussions on the mailing list: - Implement a set of plugin APIs and use them to move all of the existing MySQL replication into a (set of) plugins. - Design the APIs so that they can support full MySQL replication, but also so that they do not hardcode assumptions about how this replication implementation is done, and so that they will be suitable for other types of replication (Tungsten, Galera, parallel replication, ...). - APIs need to include the concept of a global transaction ID. Need to determine the extent to which the semantics of such ID will be defined by the API, and to which extend it will be defined by the plugin implementations. - APIs should properly support reliable crash-recovery with decent performance (eg. not require multiple mandatory fsync()s per commit, and not make group commit impossible). - Would be nice if the API provided facilities for implementing good consistency checking support (mainly checking master tables against slave tables is hard here I think, but also applying wrong binlog data and individual event checksums). Steps to make this more concrete: - Investigate the current MySQL replication, and list all of the places where a plugin implementation will need to connect/hook into the MySQL server. * handler::{write,update,delete}_row() * Statement execution * Transaction start/commit * Table open * Query safe/not/safe for statement based replication * Statement-based logging details (user variables, random seed, etc.) * ... - Use this list to make an initial sketch of the set of APIs we need. - Use the list to determine the feasibility of this project and the level of detail in the API needed to support a full replication implementation as a plugin. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Progress (by Knielsen): New replication APIs (107)
by worklog-noreply＠askmonty.org 29 Mar '10

29 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: New replication APIs CREATION DATE..: Mon, 15 Mar 2010, 13:55 SUPERVISOR.....: Knielsen IMPLEMENTOR....: Knielsen COPIES TO......: Sergei CATEGORY.......: Server-Sprint TASK ID........: 107 (http://askmonty.org/worklog/?tid=107) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 50 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Knielsen - Mon, 29 Mar 2010, 07:33)=-=- Research and design discussions: Galera, 2pc/XA, group commit, multi-engine transactions. Worked 14 hours and estimate 0 hours remain (original estimate increased by 14 hours). -=-=(Knielsen - Wed, 24 Mar 2010, 10:39)=-=- Design discussions Worked 11 hours and estimate 0 hours remain (original estimate increased by 11 hours). -=-=(Knielsen - Mon, 15 Mar 2010, 14:28)=-=- Research into the problem, and discussions on phone/mailing list Worked 25 hours and estimate 0 hours remain (original estimate increased by 25 hours). -=-=(Guest - Mon, 15 Mar 2010, 14:18)=-=- High-Level Specification modified. --- /tmp/wklog.107.old.9086 2010-03-15 14:18:18.000000000 +0000 +++ /tmp/wklog.107.new.9086 2010-03-15 14:18:18.000000000 +0000 @@ -1 +1,43 @@ +Current ideas/status after discussions on the mailing list: + + - Implement a set of plugin APIs and use them to move all of the existing + MySQL replication into a (set of) plugins. + + - Design the APIs so that they can support full MySQL replication, but also + so that they do not hardcode assumptions about how this replication + implementation is done, and so that they will be suitable for other types of + replication (Tungsten, Galera, parallel replication, ...). + + - APIs need to include the concept of a global transaction ID. Need to + determine the extent to which the semantics of such ID will be defined + by the API, and to which extend it will be defined by the plugin + implementations. + + - APIs should properly support reliable crash-recovery with decent + performance (eg. not require multiple mandatory fsync()s per commit, and + not make group commit impossible). + + - Would be nice if the API provided facilities for implementing good + consistency checking support (mainly checking master tables against slave + tables is hard here I think, but also applying wrong binlog data and + individual event checksums). + + +Steps to make this more concrete: + + - Investigate the current MySQL replication, and list all of the places where + a plugin implementation will need to connect/hook into the MySQL server. + * handler::{write,update,delete}_row() + * Statement execution + * Transaction start/commit + * Table open + * Query safe/not/safe for statement based replication + * Statement-based logging details (user variables, random seed, etc.) + * ... + + - Use this list to make an initial sketch of the set of APIs we need. + + - Use the list to determine the feasibility of this project and the level of + detail in the API needed to support a full replication implementation as a + plugin. -=-=(Serg - Mon, 15 Mar 2010, 14:13)=-=- Observers changed: Sergei DESCRIPTION: This is a top-level task for the project of designing a new set of replication APIs for MariaDB. This task is for the initial discussion of what to do and where to focus. The project is started in this email thread: https://lists.launchpad.net/maria-developers/msg01998.html HIGH-LEVEL SPECIFICATION: Current ideas/status after discussions on the mailing list: - Implement a set of plugin APIs and use them to move all of the existing MySQL replication into a (set of) plugins. - Design the APIs so that they can support full MySQL replication, but also so that they do not hardcode assumptions about how this replication implementation is done, and so that they will be suitable for other types of replication (Tungsten, Galera, parallel replication, ...). - APIs need to include the concept of a global transaction ID. Need to determine the extent to which the semantics of such ID will be defined by the API, and to which extend it will be defined by the plugin implementations. - APIs should properly support reliable crash-recovery with decent performance (eg. not require multiple mandatory fsync()s per commit, and not make group commit impossible). - Would be nice if the API provided facilities for implementing good consistency checking support (mainly checking master tables against slave tables is hard here I think, but also applying wrong binlog data and individual event checksums). Steps to make this more concrete: - Investigate the current MySQL replication, and list all of the places where a plugin implementation will need to connect/hook into the MySQL server. * handler::{write,update,delete}_row() * Statement execution * Transaction start/commit * Table open * Query safe/not/safe for statement based replication * Statement-based logging details (user variables, random seed, etc.) * ... - Use this list to make an initial sketch of the set of APIs we need. - Use the list to determine the feasibility of this project and the level of detail in the API needed to support a full replication implementation as a plugin. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Guest): Make EXPLAIN show where subquery predicates are in the WHERE clause (111)
by worklog-noreply＠askmonty.org 29 Mar '10

29 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Make EXPLAIN show where subquery predicates are in the WHERE clause CREATION DATE..: Mon, 29 Mar 2010, 07:07 SUPERVISOR.....: Psergey IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 111 (http://askmonty.org/worklog/?tid=111) VERSION........: Server-5.3 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Guest - Mon, 29 Mar 2010, 07:09)=-=- Supervisor updated. --- /tmp/wklog.111.old.13259 2010-03-29 07:09:55.000000000 +0000 +++ /tmp/wklog.111.new.13259 2010-03-29 07:09:55.000000000 +0000 @@ -1 +1 @@ -Bothorsen +Psergey -=-=(Guest - Mon, 29 Mar 2010, 07:09)=-=- Category updated. --- /tmp/wklog.111.old.13259 2010-03-29 07:09:55.000000000 +0000 +++ /tmp/wklog.111.new.13259 2010-03-29 07:09:55.000000000 +0000 @@ -1 +1 @@ -Client-BackLog +Server-RawIdeaBin -=-=(Guest - Mon, 29 Mar 2010, 07:09)=-=- Version updated. --- /tmp/wklog.111.old.13259 2010-03-29 07:09:55.000000000 +0000 +++ /tmp/wklog.111.new.13259 2010-03-29 07:09:55.000000000 +0000 @@ -1 +1 @@ -Benchmarks-3.0 +9.x -=-=(Guest - Mon, 29 Mar 2010, 07:09)=-=- Version updated. --- /tmp/wklog.111.old.13259 2010-03-29 07:09:55.000000000 +0000 +++ /tmp/wklog.111.new.13259 2010-03-29 07:09:55.000000000 +0000 @@ -1 +1 @@ -9.x +Server-5.3 DESCRIPTION: Current EXPLAIN does not show where the subquery predicate is attached to. For example, see [1], slide#5 "Straightforward subquery evaluation (contd)", or look here: MariaDB [test]> explain select * from ot1, ot2 where ot1.a=ot2.a and (ot2.a in (select it1.b from it1) or ot1.b<3); +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+ | 1 | PRIMARY | ot1 | ALL | NULL | NULL | NULL | NULL | 10 | Using where | | 1 | PRIMARY | ot2 | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Using join buffer | | 2 | SUBQUERY | it1 | ALL | NULL | NULL | NULL | NULL | 20 | | +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+ 3 rows in set (0.01 sec) Here there are two "Using where", and it is not clear where the predicate is attached to. If one has sufficient knowledge, they could know that - "ot2.a in (select )" will be substituted for "ot1.a" (provided datatypes allow equality propagation) - For correlated subqueries, equality propagation will not affect outside references (so, if the subquery in the above example was correlated, it would have been attached to table ot2, not ot1). As one can see, the rules are quite complicated. The full solution would be to show expressions behind "Using Where", but that has additional complications due to expressions being too long for current EXPLAIN output format. A simplified solution would be to show "Subquery#n" in Extra column if the clause in "Using Where" has a subquery. [1] http://forge.mysql.com/wiki/Image:NewSubqueryOptimizationsIn6_0_UC2008.pdf ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] New (by Psergey): Make EXPLAIN show where subquery predicates are in the WHERE clause (111)
by worklog-noreply＠askmonty.org 29 Mar '10

29 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Make EXPLAIN show where subquery predicates are in the WHERE clause CREATION DATE..: Mon, 29 Mar 2010, 07:07 SUPERVISOR.....: Bothorsen IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Client-BackLog TASK ID........: 111 (http://askmonty.org/worklog/?tid=111) VERSION........: Benchmarks-3.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: Current EXPLAIN does not show where the subquery predicate is attached to. For example, see [1], slide#5 "Straightforward subquery evaluation (contd)", or look here: MariaDB [test]> explain select * from ot1, ot2 where ot1.a=ot2.a and (ot2.a in (select it1.b from it1) or ot1.b<3); +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+ | 1 | PRIMARY | ot1 | ALL | NULL | NULL | NULL | NULL | 10 | Using where | | 1 | PRIMARY | ot2 | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Using join buffer | | 2 | SUBQUERY | it1 | ALL | NULL | NULL | NULL | NULL | 20 | | +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+ 3 rows in set (0.01 sec) Here there are two "Using where", and it is not clear where the predicate is attached to. If one has sufficient knowledge, they could know that - "ot2.a in (select )" will be substituted for "ot1.a" (provided datatypes allow equality propagation) - For correlated subqueries, equality propagation will not affect outside references (so, if the subquery in the above example was correlated, it would have been attached to table ot2, not ot1). As one can see, the rules are quite complicated. The full solution would be to show expressions behind "Using Where", but that has additional complications due to expressions being too long for current EXPLAIN output format. A simplified solution would be to show "Subquery#n" in Extra column if the clause in "Using Where" has a subquery. [1] http://forge.mysql.com/wiki/Image:NewSubqueryOptimizationsIn6_0_UC2008.pdf ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] New (by Psergey): Make EXPLAIN show where subquery predicates are in the WHERE clause (111)
by worklog-noreply＠askmonty.org 29 Mar '10

29 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Make EXPLAIN show where subquery predicates are in the WHERE clause CREATION DATE..: Mon, 29 Mar 2010, 07:07 SUPERVISOR.....: Bothorsen IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Client-BackLog TASK ID........: 111 (http://askmonty.org/worklog/?tid=111) VERSION........: Benchmarks-3.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: Current EXPLAIN does not show where the subquery predicate is attached to. For example, see [1], slide#5 "Straightforward subquery evaluation (contd)", or look here: MariaDB [test]> explain select * from ot1, ot2 where ot1.a=ot2.a and (ot2.a in (select it1.b from it1) or ot1.b<3); +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+ | 1 | PRIMARY | ot1 | ALL | NULL | NULL | NULL | NULL | 10 | Using where | | 1 | PRIMARY | ot2 | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Using join buffer | | 2 | SUBQUERY | it1 | ALL | NULL | NULL | NULL | NULL | 20 | | +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+ 3 rows in set (0.01 sec) Here there are two "Using where", and it is not clear where the predicate is attached to. If one has sufficient knowledge, they could know that - "ot2.a in (select )" will be substituted for "ot1.a" (provided datatypes allow equality propagation) - For correlated subqueries, equality propagation will not affect outside references (so, if the subquery in the above example was correlated, it would have been attached to table ot2, not ot1). As one can see, the rules are quite complicated. The full solution would be to show expressions behind "Using Where", but that has additional complications due to expressions being too long for current EXPLAIN output format. A simplified solution would be to show "Subquery#n" in Extra column if the clause in "Using Where" has a subquery. [1] http://forge.mysql.com/wiki/Image:NewSubqueryOptimizationsIn6_0_UC2008.pdf ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): Make EXPLAIN always show materialization separately (110)
by worklog-noreply＠askmonty.org 28 Mar '10

28 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Make EXPLAIN always show materialization separately CREATION DATE..: Mon, 29 Mar 2010, 06:45 SUPERVISOR.....: Igor IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 110 (http://askmonty.org/worklog/?tid=110) VERSION........: Server-5.3 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Mon, 29 Mar 2010, 06:46)=-=- Low Level Design modified. --- /tmp/wklog.110.old.11745 2010-03-29 06:46:30.000000000 +0000 +++ /tmp/wklog.110.new.11745 2010-03-29 06:46:30.000000000 +0000 @@ -1 +1,8 @@ +For now, all changes will be in select_describe(): +- In the for-each-join-table loop, when we've reached a line where we would + have printed "[Start ]Materialize;" in Extra column, remember the table + number, and emit a materialized table access line instead +- After the loop, do another loop over remembered materialization nests and + print them (a possible difficulty: do we remember what select# they are + from?) -=-=(Psergey - Mon, 29 Mar 2010, 06:46)=-=- High-Level Specification modified. --- /tmp/wklog.110.old.11654 2010-03-29 06:46:19.000000000 +0000 +++ /tmp/wklog.110.new.11654 2010-03-29 06:46:19.000000000 +0000 @@ -1 +1,15 @@ +Materialized table access line will look as follows: +Table name +---------- +* Table name will be "SUBQUERY#%d" where %d will refer to the id of first + select in the subquery (when the subquery is a UNION it would be better + to refe to the union-operation line but it has id=NULL so it's not easy + to refer to it) + +Access method +------------- +* SJ-Materialization-lookup will have eq_ref on 'distinct_key' +* SJ-Materialization-scan will have access method ALL, with #rows being + expected number of records in the temp table (i.e. after duplicates are + removed) -=-=(Psergey - Mon, 29 Mar 2010, 06:46)=-=- Category updated. --- /tmp/wklog.110.old.11639 2010-03-29 06:46:02.000000000 +0000 +++ /tmp/wklog.110.new.11639 2010-03-29 06:46:02.000000000 +0000 @@ -1 +1 @@ -Client-BackLog +Server-RawIdeaBin -=-=(Psergey - Mon, 29 Mar 2010, 06:46)=-=- Version updated. --- /tmp/wklog.110.old.11639 2010-03-29 06:46:02.000000000 +0000 +++ /tmp/wklog.110.new.11639 2010-03-29 06:46:02.000000000 +0000 @@ -1 +1 @@ -Benchmarks-3.0 +9.x -=-=(Psergey - Mon, 29 Mar 2010, 06:46)=-=- Version updated. --- /tmp/wklog.110.old.11639 2010-03-29 06:46:02.000000000 +0000 +++ /tmp/wklog.110.new.11639 2010-03-29 06:46:02.000000000 +0000 @@ -1 +1 @@ -9.x +Server-5.3 DESCRIPTION: At the moment, SJM-Materialization is shown in EXPLAIN output in this way: MariaDB [j45]> explain select * from ot where a in (select b from it1); +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | PRIMARY | ot | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | PRIMARY | it1 | ALL | NULL | NULL | NULL | NULL | 10 | Materialize | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ MariaDB [j45]> explain select * from ot where a in (select it1.b from it1, it2); +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+ | 1 | PRIMARY | ot | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | PRIMARY | it1 | ALL | NULL | NULL | NULL | NULL | 10 | Start materialize | | 1 | PRIMARY | it2 | ALL | NULL | NULL | NULL | NULL | 10 | End materialize; Using join buffer | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+ This WL task is to change the output format so it will look as follows: - Tables inside the SJM-nest are displayed as a separate select - within the master select, there is a line that denotes SJM-lookup or SJM-Scan. The above examples will look as follows: MariaDB [j45]> explain select * from ot where a in (select b from it1); +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------+ | 1 | PRIMARY | ot | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | PRIMARY | SUBQUERY#2 | eq_ref | distinct_key | distinct_key | 5 | j45.ot.a | 1 | | | 2 | SUBQUERY | it1 | ALL | NULL | NULL | NULL | NULL | 10 | | +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------+ MariaDB [j45]> explain select * from ot where a in (select it1.b from it1, it2); +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------------------+ | 1 | PRIMARY | ot | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | PRIMARY | SUBQUERY#2 | eq_ref | distinct_key | distinct_key | 5 | j45.ot.a | 1 | | | 2 | SUBQUERY | it1 | ALL | NULL | NULL | NULL | NULL | 10 | | | 2 | SUBQUERY | it2 | ALL | NULL | NULL | NULL | NULL | 10 | Using join buffer | +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------------------+ The rationale behind the change is: - Unification of EXPLAIN output with MWL#90 - The new format is more natural representation of what is going on, conceptually-wise (and may be soon be code-wise) - The new format allows to display E(#records-in-temp-table) for the SJM-Scan case (and for SJM-lookup that number doesn't matter that much) - The new format doesn't put anything into "Extra" column and that's good because that column is already overloaded and horizontal screen space is precious (while vertical is not so much). HIGH-LEVEL SPECIFICATION: Materialized table access line will look as follows: Table name ---------- * Table name will be "SUBQUERY#%d" where %d will refer to the id of first select in the subquery (when the subquery is a UNION it would be better to refe to the union-operation line but it has id=NULL so it's not easy to refer to it) Access method ------------- * SJ-Materialization-lookup will have eq_ref on 'distinct_key' * SJ-Materialization-scan will have access method ALL, with #rows being expected number of records in the temp table (i.e. after duplicates are removed) LOW-LEVEL DESIGN: For now, all changes will be in select_describe(): - In the for-each-join-table loop, when we've reached a line where we would have printed "[Start ]Materialize;" in Extra column, remember the table number, and emit a materialized table access line instead - After the loop, do another loop over remembered materialization nests and print them (a possible difficulty: do we remember what select# they are from?) ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): Make EXPLAIN always show materialization separately (110)
by worklog-noreply＠askmonty.org 28 Mar '10

28 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Make EXPLAIN always show materialization separately CREATION DATE..: Mon, 29 Mar 2010, 06:45 SUPERVISOR.....: Igor IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 110 (http://askmonty.org/worklog/?tid=110) VERSION........: Server-5.3 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Mon, 29 Mar 2010, 06:46)=-=- Low Level Design modified. --- /tmp/wklog.110.old.11745 2010-03-29 06:46:30.000000000 +0000 +++ /tmp/wklog.110.new.11745 2010-03-29 06:46:30.000000000 +0000 @@ -1 +1,8 @@ +For now, all changes will be in select_describe(): +- In the for-each-join-table loop, when we've reached a line where we would + have printed "[Start ]Materialize;" in Extra column, remember the table + number, and emit a materialized table access line instead +- After the loop, do another loop over remembered materialization nests and + print them (a possible difficulty: do we remember what select# they are + from?) -=-=(Psergey - Mon, 29 Mar 2010, 06:46)=-=- High-Level Specification modified. --- /tmp/wklog.110.old.11654 2010-03-29 06:46:19.000000000 +0000 +++ /tmp/wklog.110.new.11654 2010-03-29 06:46:19.000000000 +0000 @@ -1 +1,15 @@ +Materialized table access line will look as follows: +Table name +---------- +* Table name will be "SUBQUERY#%d" where %d will refer to the id of first + select in the subquery (when the subquery is a UNION it would be better + to refe to the union-operation line but it has id=NULL so it's not easy + to refer to it) + +Access method +------------- +* SJ-Materialization-lookup will have eq_ref on 'distinct_key' +* SJ-Materialization-scan will have access method ALL, with #rows being + expected number of records in the temp table (i.e. after duplicates are + removed) -=-=(Psergey - Mon, 29 Mar 2010, 06:46)=-=- Category updated. --- /tmp/wklog.110.old.11639 2010-03-29 06:46:02.000000000 +0000 +++ /tmp/wklog.110.new.11639 2010-03-29 06:46:02.000000000 +0000 @@ -1 +1 @@ -Client-BackLog +Server-RawIdeaBin -=-=(Psergey - Mon, 29 Mar 2010, 06:46)=-=- Version updated. --- /tmp/wklog.110.old.11639 2010-03-29 06:46:02.000000000 +0000 +++ /tmp/wklog.110.new.11639 2010-03-29 06:46:02.000000000 +0000 @@ -1 +1 @@ -Benchmarks-3.0 +9.x -=-=(Psergey - Mon, 29 Mar 2010, 06:46)=-=- Version updated. --- /tmp/wklog.110.old.11639 2010-03-29 06:46:02.000000000 +0000 +++ /tmp/wklog.110.new.11639 2010-03-29 06:46:02.000000000 +0000 @@ -1 +1 @@ -9.x +Server-5.3 DESCRIPTION: At the moment, SJM-Materialization is shown in EXPLAIN output in this way: MariaDB [j45]> explain select * from ot where a in (select b from it1); +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | PRIMARY | ot | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | PRIMARY | it1 | ALL | NULL | NULL | NULL | NULL | 10 | Materialize | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ MariaDB [j45]> explain select * from ot where a in (select it1.b from it1, it2); +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+ | 1 | PRIMARY | ot | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | PRIMARY | it1 | ALL | NULL | NULL | NULL | NULL | 10 | Start materialize | | 1 | PRIMARY | it2 | ALL | NULL | NULL | NULL | NULL | 10 | End materialize; Using join buffer | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+ This WL task is to change the output format so it will look as follows: - Tables inside the SJM-nest are displayed as a separate select - within the master select, there is a line that denotes SJM-lookup or SJM-Scan. The above examples will look as follows: MariaDB [j45]> explain select * from ot where a in (select b from it1); +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------+ | 1 | PRIMARY | ot | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | PRIMARY | SUBQUERY#2 | eq_ref | distinct_key | distinct_key | 5 | j45.ot.a | 1 | | | 2 | SUBQUERY | it1 | ALL | NULL | NULL | NULL | NULL | 10 | | +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------+ MariaDB [j45]> explain select * from ot where a in (select it1.b from it1, it2); +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------------------+ | 1 | PRIMARY | ot | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | PRIMARY | SUBQUERY#2 | eq_ref | distinct_key | distinct_key | 5 | j45.ot.a | 1 | | | 2 | SUBQUERY | it1 | ALL | NULL | NULL | NULL | NULL | 10 | | | 2 | SUBQUERY | it2 | ALL | NULL | NULL | NULL | NULL | 10 | Using join buffer | +----+-------------+------------+--------+---------------+--------------+---------+----------+------+-------------------+ The rationale behind the change is: - Unification of EXPLAIN output with MWL#90 - The new format is more natural representation of what is going on, conceptually-wise (and may be soon be code-wise) - The new format allows to display E(#records-in-temp-table) for the SJM-Scan case (and for SJM-lookup that number doesn't matter that much) - The new format doesn't put anything into "Extra" column and that's good because that column is already overloaded and horizontal screen space is precious (while vertical is not so much). HIGH-LEVEL SPECIFICATION: Materialized table access line will look as follows: Table name ---------- * Table name will be "SUBQUERY#%d" where %d will refer to the id of first select in the subquery (when the subquery is a UNION it would be better to refe to the union-operation line but it has id=NULL so it's not easy to refer to it) Access method ------------- * SJ-Materialization-lookup will have eq_ref on 'distinct_key' * SJ-Materialization-scan will have access method ALL, with #rows being expected number of records in the temp table (i.e. after duplicates are removed) LOW-LEVEL DESIGN: For now, all changes will be in select_describe(): - In the for-each-join-table loop, when we've reached a line where we would have printed "[Start ]Materialize;" in Extra column, remember the table number, and emit a materialized table access line instead - After the loop, do another loop over remembered materialization nests and print them (a possible difficulty: do we remember what select# they are from?) ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): Make EXPLAIN always show materialization separately (110)
by worklog-noreply＠askmonty.org 28 Mar '10

28 Mar '10

1 0

[Maria-developers] Updated (by Psergey): Make EXPLAIN always show materialization separately (110)
by worklog-noreply＠askmonty.org 28 Mar '10

28 Mar '10

1 0