maria-developers team mailing list archive

[worklog-noreply@xxxxxxxxxxxx]

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