List:General Discussion« Previous MessageNext Message »
From:Yang Zhang Date:February 24 2010 3:11pm
Subject:EXPLAIN says DEPENDENT SUBQUERY despite no free variables
View as plain text  
I have the following query. Note that the nested query has no
dependencies on the outer one, yet mysql reports it as dependent.
Furthermore, it says the join type is an ALL (nested loop join, the
slowest possible one, in which each row of the outer table results in
a complete inner table scan), whereas I know that the subquery yields
only 50 tuples, so a const join would've made more sense. Any ideas on
how to optimize this by convincing mysql to see the independence use a
const join? (This is in mysql 5.4.3 beta.) Thanks in advance.

mysql> explain
      select thread_id, argument, event_time
      from general_log
      where command_type in ("Query", "Execute") and thread_id in (
        select distinct thread_id
        from general_log
        where
          (
            (command_type = "Init DB" and argument like "tpcc50") or
            (command_type = "Connect" and argument like "%tpcc50")
          ) and
          thread_id > 0
      )
      order by thread_id, event_time desc;

+----+--------------------+-------------+------+---------------+------+---------+------+-----------+----------+------------------------------+
| id | select_type        | table       | type | possible_keys | key
| key_len | ref  | rows      | filtered | Extra
|
+----+--------------------+-------------+------+---------------+------+---------+------+-----------+----------+------------------------------+
|  1 | PRIMARY            | general_log | ALL  | NULL          | NULL
| NULL    | NULL | 335790898 |   100.00 | Using where; Using filesort
|
|  2 | DEPENDENT SUBQUERY | general_log | ALL  | NULL          | NULL
| NULL    | NULL | 335790898 |   100.00 | Using where; Using temporary
|
+----+--------------------+-------------+------+---------------+------+---------+------+-----------+----------+------------------------------+
2 rows in set, 1 warning (0.04 sec)
-- 
Yang Zhang
http://www.mit.edu/~y_z/
Thread
EXPLAIN says DEPENDENT SUBQUERY despite no free variablesYang Zhang24 Feb
  • Re: EXPLAIN says DEPENDENT SUBQUERY despite no free variablesPerrin Harkins24 Feb
    • Re: EXPLAIN says DEPENDENT SUBQUERY despite no free variablesDan Nelson25 Feb
      • Re: EXPLAIN says DEPENDENT SUBQUERY despite no free variablesJohan De Meersman25 Feb
      • Re: EXPLAIN says DEPENDENT SUBQUERY despite no free variablesPerrin Harkins25 Feb
  • Re: EXPLAIN says DEPENDENT SUBQUERY despite no free variablesBaron Schwartz25 Feb