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