CHEAP SQ: A query with LEFT JOIN in outer SELECT and INNER JOIN and aggregate in subquery takes much longer than on the main tree

SELECT * FROM t1 LEFT JOIN t2 ON ( c = e )

WHERE d IN ( SELECT SUM(a) FROM t1, t2 WHERE f = b );

date: 2012-06-01 14:10:15 +0300

revno: 3407

revision-id: wlad@montyprogram.com-20120530182054-g7sll9u4gsitvgqe

date: 2012-05-30 20:20:54 +0200

revno: 3425

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	t2	ALL	NULL	NULL	NULL	NULL	274	100.00	Using where

1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	655	100.00	Using where; Using join buffer (flat, BNL join)

2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	274	100.00	

2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	655	100.00	Using where; Using join buffer (flat, BNL join)

Warnings:

Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t2`.`e` AS `e`,`test`.`t2`.`f` AS `f` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`c` = `test`.`t2`.`e`) and <expr_cache><`test`.`t2`.`d`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select sum(`test`.`t1`.`a`) from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`b` = `test`.`t2`.`f`) having (<cache>(`test`.`t2`.`d`) = <ref_null_helper>(sum(`test`.`t1`.`a`)))))))

SELECT * FROM t1 LEFT JOIN t2 ON ( c = e )

WHERE d IN ( SELECT SUM(a) FROM t1, t2 WHERE f = b );

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	655	100.00	

1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	274	100.00	Using where; Using join buffer (flat, BNL join)

2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	274	100.00	

2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	655	100.00	Using where; Using join buffer (flat, BNL join)

Warnings:

Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t2`.`e` AS `e`,`test`.`t2`.`f` AS `f` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`e` = `test`.`t1`.`c`)) where <expr_cache><`test`.`t2`.`d`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select sum(`test`.`t1`.`a`) from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`b` = `test`.`t2`.`f`) having (<cache>(`test`.`t2`.`d`) = <ref_null_helper>(sum(`test`.`t1`.`a`))))))

SET optimizer_switch = 'in_to_exists=on';

CREATE TABLE t1 (a INT, b VARCHAR(3), c VARCHAR(3));

INSERT INTO t1 VALUES 

(12,'N/A','Abi'),(22,'USA','Akr'),(9,'USA','Alb'),(45,'USA','Alb'),

(13,'USA','Ale'),(11,'N/A','All'),(17,'CAN','Ama'),(33,'USA','Ana'),

(26,'FRA','Anc'),(11,'USA','Ann'),(9,'USA','Ard'),(33,'USA','Arl'),

(17,'USA','Arl'),(10,'CAN','Arv'),(10,'USA','Ath'),(42,'USA','Atl'),

(20,'USA','Aug'),(28,'USA','Aur'),(14,'N/A','Aur'),(66,'USA','Aus'),

(25,'USA','Bak'),(65,'USA','Bal'),(23,'USA','Bat'),(11,'USA','Bea'),

(11,'CAN','Bel'),(10,'FRA','Ber'),(9,'USA','Bil'),(24,'USA','Bir'),

(19,'USA','Boi'),(59,'N/A','Bos'),(9,'USA','Bou'),(14,'USA','Bri'),

(9,'USA','Bro'),(14,'USA','Bro'),(29,'USA','Buf'),(10,'USA','Bur'),

(10,'USA','Cam'),(10,'USA','Cap'),(11,'USA','Car'),(9,'USA','Car'),

(9,'USA','Car'),(12,'USA','Ced'),(18,'USA','Cha'),(9,'USA','Cha'),

(54,'USA','Cha'),(16,'USA','Cha'),(20,'USA','Che'),(290,'USA','Chi'),

(17,'USA','Chu'),(33,'USA','Cin'),(10,'USA','Cit'),(11,'FRA','Cla'),

(10,'CAN','Cle'),(48,'USA','Cle'),(36,'USA','Col'),(12,'USA','Col'),

(71,'USA','Col'),(19,'N/A','Col'),(9,'USA','Com'),(12,'USA','Con'),

(12,'USA','Cor'),(12,'USA','Cor'),(28,'USA','Cor'),(11,'USA','Cos'),

(119,'USA','Dal'),(10,'USA','Dal'),(10,'CAN','Dav'),(17,'USA','Day'),

(55,'USA','Den'),(20,'USA','Des'),(95,'USA','Det'),(11,'USA','Dow'),

(19,'USA','Dur'),(13,'USA','Eas'),(9,'USA','El '),(12,'USA','El '),

(56,'N/A','El '),(9,'USA','Elg'),(12,'USA','Eli'),(10,'CAN','Eri'),

(13,'USA','Esc'),(14,'USA','Eug'),(12,'USA','Eva'),(9,'USA','Fai'),

(9,'USA','Fal'),(12,'USA','Fay'),(12,'USA','Fli'),(13,'FRA','Fon'),

(12,'USA','For'),(15,'USA','For'),(21,'USA','For'),(53,'USA','For'),

(20,'USA','Fre'),(43,'USA','Fre'),(13,'USA','Ful'),(9,'USA','Gai'),

(17,'USA','Gar'),(22,'N/A','Gar'),(10,'USA','Gar'),(11,'USA','Gil'),

(22,'USA','Gle'),(19,'USA','Gle'),(13,'CAN','Gra'),(20,'USA','Gra'),

(10,'USA','Gre'),(22,'USA','Gre'),(15,'USA','Ham'),(12,'USA','Har'),

(14,'USA','Hay'),(18,'USA','Hen'),(23,'USA','Hia'),(14,'USA','Hol'),

(37,'USA','Hon'),(195,'USA','Hou'),(19,'USA','Hun'),(16,'USA','Hun'),

(11,'USA','Ind'),(79,'FRA','Ind'),(11,'USA','Ing'),(14,'N/A','Irv'),

(19,'CAN','Irv'),(18,'USA','Jac'),(74,'USA','Jac'),(24,'USA','Jer'),

(11,'USA','Jol'),(44,'USA','Kan'),(15,'USA','Kan'),(9,'USA','Ken'),

(17,'USA','Kno'),(11,'USA','Laf'),(14,'USA','Lak'),(12,'USA','Lan'),

(12,'USA','Lan'),(18,'USA','Lar'),(48,'FRA','Las'),(26,'USA','Lex'),

(23,'N/A','Lin'),(18,'USA','Lit'),(10,'USA','Liv'),(46,'USA','Lon'),

(369,'USA','Los'),(26,'USA','Lou'),(11,'USA','Low'),(20,'USA','Lub'),

(11,'USA','Mac'),(21,'USA','Mad'),(11,'USA','Man'),(11,'USA','McA'),

(65,'USA','Mem'),(40,'USA','Mes'),(12,'USA','Mes'),(15,'N/A','Met'),

(36,'USA','Mia'),(10,'FRA','Mia'),(10,'USA','Mid'),(60,'USA','Mil'),

(38,'CAN','Min'),(10,'USA','Mis'),(20,'USA','Mob'),(19,'USA','Mod'),

(20,'USA','Mon'),(14,'N/A','Mor'),(13,'USA','Nap'),(57,'USA','Nas'),

(9,'USA','New'),(12,'USA','New'),(48,'USA','New'),(801,'USA','New'),

(27,'N/A','New'),(18,'USA','New'),(23,'USA','Nor'),(9,'CAN','Nor'),

(12,'USA','Nor'),(10,'FRA','Nor'),(40,'USA','Oak'),(16,'USA','Oce'),

(9,'USA','Ode'),(51,'USA','Okl'),(39,'USA','Oma'),(16,'USA','Ont'),

(13,'USA','Ora'),(19,'USA','Orl'),(15,'FRA','Ove'),(17,'N/A','Oxn'),

(12,'USA','Pal'),(12,'USA','Par'),(14,'USA','Pas'),(13,'USA','Pas'),

(15,'CAN','Pat'),(14,'USA','Pem'),(11,'USA','Peo'),(11,'USA','Peo'),

(152,'N/A','Phi'),(132,'USA','Pho'),(33,'USA','Pit'),(22,'USA','Pla'),

(15,'USA','Pom'),(53,'USA','Por'),(10,'USA','Por'),(17,'USA','Pro'),

(11,'USA','Pro'),(10,'USA','Pue'),(28,'FRA','Ral'),(13,'CAN','Ran'),

(18,'USA','Ren'),(20,'USA','Ric'),(9,'USA','Ric'),(26,'USA','Riv'),

(9,'USA','Roa'),(22,'N/A','Roc'),(15,'USA','Roc'),(41,'USA','Sac'),

(35,'USA','Sai'),(29,'USA','Sai'),(25,'USA','Sai'),(14,'USA','Sal'),

(15,'USA','Sal'),(18,'USA','Sal'),(114,'FRA','San'),(19,'USA','San'),

(10,'USA','San'),(122,'USA','San'),(78,'CAN','San'),(89,'USA','San'),

(9,'USA','San'),(10,'USA','San'),(34,'USA','San'),(10,'USA','San'),

(15,'N/A','San'),(9,'USA','San'),(15,'USA','San'),(13,'USA','Sav'),

(20,'USA','Sco'),(56,'FRA','Sea'),(20,'CAN','Shr'),(11,'USA','Sim'),

(12,'USA','Sio'),(11,'USA','Sou'),(20,'USA','Spo'),(15,'USA','Spr'),

(15,'USA','Spr'),(11,'USA','Spr'),(12,'USA','Sta'),(12,'USA','Ste'),

(24,'USA','Sto'),(13,'USA','Sun'),(10,'FRA','Sun'),(15,'USA','Syr'),

(19,'N/A','Tac'),(15,'USA','Tal'),(30,'USA','Tam'),(16,'USA','Tem'),

(12,'CAN','Tho'),(31,'USA','Tol'),(12,'USA','Top'),(14,'USA','Tor'),

(49,'USA','Tuc'),(39,'FRA','Tul'),(12,'USA','Val'),(14,'USA','Van'),

(43,'USA','Vir'),(9,'USA','Vis'),(11,'USA','Wac'),(14,'USA','War'),

(57,'USA','Was'),(11,'USA','Wat'),(11,'FRA','Wes'),(11,'USA','Wes'),

(10,'USA','Wes'),(34,'USA','Wic'),(10,'USA','Wic'),(19,'USA','Win'),

(17,'FRA','Wor'),(20,'USA','Yon');

CREATE TABLE t2 (d INT, e VARCHAR(3), f VARCHAR(3));

INSERT INTO t2 SELECT * FROM t1;

INSERT INTO t1 SELECT * FROM t1;

SELECT * FROM t1 LEFT JOIN t2 ON ( c = e )

WHERE d IN ( SELECT SUM(a) FROM t1, t2 WHERE f = b );