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