SET optimizer_switch = 'semijoin=on';

# Any value > 2 will do

SET join_cache_level = 3;

CREATE TABLE t1 (pk1 INT PRIMARY KEY, i1 INT) ENGINE=MyISAM;

INSERT INTO t1 VALUES (1,4),(2,5);

CREATE TABLE t2 (i2 INT, c2 CHAR(1)) ENGINE=MyISAM;

INSERT INTO t2 VALUES (1,'v'),(7,'s');

CREATE TABLE t3 (pk3 INT PRIMARY KEY, i3 INT, c3 CHAR(1), INDEX(i3), INDEX(c3,i3)) ENGINE=MyISAM;

INSERT INTO t3 VALUES (1,7,'g'),(2,4,'p'),(3,1,'q');

CREATE TABLE t4 (i4 INT) ENGINE=MyISAM;

INSERT INTO t4 VALUES (1);

SELECT * FROM t1, t2 LEFT JOIN t3 ON (c3 = c2) 

WHERE ( i2, pk1 )  IN ( SELECT i3, 1 FROM t3, t4 ) ORDER BY i1, c2;

pk1	i1	i2	c2	pk3	i3	c3

1	4	7	s	NULL	NULL	NULL

pk1	i1	i2	c2	pk3	i3	c3

1	4	7	s	NULL	NULL	NULL

1	4	1	v	NULL	NULL	NULL

WHERE ( i2, pk1 )  IN ( SELECT i3, 1 FROM t3, t4 ) ORDER BY i1, c2;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	PRIMARY	t4	system	NULL	NULL	NULL	NULL	1	100.00	Using filesort

1	PRIMARY	t1	const	PRIMARY	PRIMARY	4	const	1	100.00	

1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where

1	PRIMARY	t3	hash_index	i3	#hash#i3:i3	5:5	test.t2.i2	3	66.67	Start temporary; End temporary; Using join buffer (flat, BNLH join)

1	PRIMARY	t3	hash_ALL	c3	#hash#c3	2	test.t2.c2	3	33.33	Using where; Using join buffer (flat, BNLH join)

Warnings:

Note	1003	select 1 AS `pk1`,4 AS `i1`,`test`.`t2`.`i2` AS `i2`,`test`.`t2`.`c2` AS `c2`,`test`.`t3`.`pk3` AS `pk3`,`test`.`t3`.`i3` AS `i3`,`test`.`t3`.`c3` AS `c3` from `test`.`t1` semi join (`test`.`t3`) join `test`.`t2` left join `test`.`t3` on(((`test`.`t3`.`c3` = `test`.`t2`.`c2`) and (`test`.`t2`.`c2` is not null))) where (`test`.`t3`.`i3` = `test`.`t2`.`i2`) order by 4,`test`.`t2`.`c2`