SELECT a, COUNT(*) AS cnt 

FROM t1 LEFT JOIN t2 ON (d = b) 

WHERE a IN ( SELECT c FROM t2 WHERE b > 'k' ) 

GROUP BY a ORDER BY a, cnt LIMIT 2

revision-id: psergey@askmonty.org-20120705103901-9ero4x0eifk0wtks

date: 2012-07-05 14:39:01 +0400

build-date: 2012-07-11 01:54:42 +0400

revno: 3462

semijoin=on

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	PRIMARY	t1	index	a	a	4	NULL	3	100.00	Using where; Using temporary; Using filesort

1	PRIMARY	t2	ref	c	c	5	test.t1.a	2	100.00	Using index; Start temporary; End temporary

1	PRIMARY	t2	ref	d	d	2	test.t1.b	2	100.00	Using where; Using index

Warnings:

Note	1276	Field or reference 'test.t1.b' of SELECT #2 was resolved in SELECT #1

Note	1003	select `test`.`t1`.`a` AS `a`,count(0) AS `cnt` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t2` on(((`test`.`t2`.`d` = `test`.`t1`.`b`) and (`test`.`t1`.`b` is not null))) where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (`test`.`t1`.`b` > 'k')) group by `test`.`t1`.`a` order by `test`.`t1`.`a`,count(0) limit 2

SET join_cache_level = 5;

SET optimizer_switch = 'semijoin=on';

CREATE TABLE t1 (a INT NOT NULL, b CHAR(1), KEY(a)) ENGINE=MyISAM;

INSERT INTO t1 VALUES (4,'p'),(1,'q'),(8,'e');

CREATE TABLE t2 (c INT, d CHAR(1), KEY(c), KEY(d)) ENGINE=MyISAM;

INSERT INTO t2 VALUES (4,'f'),(2,'i'),(5,'h'),(3,'q'),(1,'g');

SELECT a, COUNT(*) AS cnt 

FROM t1 LEFT JOIN t2 ON (d = b) 

WHERE a IN ( SELECT c FROM t2 WHERE b > 'k' ) 

GROUP BY a ORDER BY a, cnt LIMIT 2;

a	cnt

1	1

4	1

a	cnt

1	1

SELECT a 

FROM t1 LEFT JOIN t2 ON (d = b) 

WHERE a IN ( SELECT c FROM t2 WHERE b > 'k' ) 

GROUP BY a ORDER BY a, a LIMIT 2;