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