|
EXPLAIN SELECT alias1.b FROM t1 AS alias1 LEFT JOIN t1 AS alias2 ON (alias2.b = alias1.a)
|
WHERE EXISTS ( SELECT * FROM t1 WHERE a <= alias2.a )
|
HAVING alias1.b > 10 ORDER BY alias1.b;
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY alias1 ALL NULL NULL NULL NULL 9 Using filesort
|
1 PRIMARY alias2 ref b b 5 test.alias1.a 2 Using where
|
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 9 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 9 Using where; Using filesort
|
1 PRIMARY alias2 ref b b 5 test.alias1.a 2 Using where
|
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using where
|
bzr version-info
revision-id: psergey@askmonty.org-20120801115152-qgxtqd0a5vlqrrhx
|
date: 2012-08-01 15:51:52 +0400
|
build-date: 2012-08-02 16:12:26 +0300
|
revno: 3461
|
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, b INT, KEY(b)) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES
|
(0,4),(8,6),(1,3),(8,5),(9,3),(2,4),(6,2),(1,9),(6,3);
|
|
|
let $query =
|
SELECT alias1.b FROM t1 AS alias1 LEFT JOIN t1 AS alias2 ON (alias2.b = alias1.a)
|
WHERE EXISTS ( SELECT * FROM t1 WHERE a <= alias2.a )
|
HAVING alias1.b > 10 ORDER BY alias1.b;
|
|
|
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;
|
|
|