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/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
