|
EXPLAIN SELECT *
|
FROM t1 AS alias1, ( SELECT * FROM t2 ) AS alias
|
WHERE EXISTS ( SELECT * FROM t2 WHERE b = c )
|
OR a <= 10;
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY alias1 ALL NULL NULL NULL NULL 14
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join)
|
3 SUBQUERY t2 ALL NULL NULL NULL NULL 20 Using where
|
#---------------
|
# SHOW EXPLAIN output:
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY alias1 ALL NULL NULL NULL NULL 14
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join)
|
3 SUBQUERY t2 ALL NULL NULL NULL NULL 20
|
bzr version-info
revision-id: psergey@askmonty.org-20120727121752-5dmlp1nkhrn01ab5
|
date: 2012-07-27 16:17:52 +0400
|
build-date: 2012-08-01 17:33:14 +0300
|
revno: 3460
|
Default optimizer_switch:
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off
|
Test case:
CREATE TABLE t1 (a INT) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES (7),(0),(9),(3),(4),(2),(5),(7),(0),(9),(3),(4),(2),(5);
|
|
CREATE TABLE t2 (b INT, c INT) ENGINE=MyISAM;
|
INSERT INTO t2 VALUES
|
(0,4),(8,6),(1,3),(8,5),(9,3),(24,246),(6,2),(1,9),(6,3),(2,8),
|
(4,1),(8,8),(4,8),(4,5),(7,7),(4,5),(1,1),(9,6),(4,2),(8,9);
|
|
|
let $query =
|
SELECT *
|
FROM t1 AS alias1, ( SELECT * FROM t2 ) AS alias
|
WHERE EXISTS ( SELECT * FROM t2 WHERE b = c )
|
OR a <= 10;
|
|
|
eval EXPLAIN $query;
|
--echo #---------------
|
--echo # SHOW EXPLAIN output:
|
|
--connect (con1,localhost,root,,)
|
--let $con_id = `SELECT CONNECTION_ID()`
|
|
--let $trials = 50
|
|
--disable_query_log
|
|
while ($trials)
|
{
|
--dec $trials
|
--let $run = 1000
|
|
--send_eval $query
|
|
--connection default
|
while ($run)
|
{
|
--error 0,1932
|
eval SHOW EXPLAIN FOR $con_id;
|
--dec $run
|
if (!$mysql_errno)
|
{
|
--let $run = 0
|
--let $trials = 0
|
--let $found = 1
|
}
|
}
|
|
--disable_result_log
|
--connection con1
|
--reap
|
--enable_result_log
|
|
}
|
|
if (!$found)
|
{
|
--echo ########### Could not catch the query by SHOW EXPLAIN, try again #############
|
}
|
|
DROP TABLE t1, t2;
|
|