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

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

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

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;