SELECT * FROM t1 AS alias1, t2 AS alias2 
WHERE NOT EXISTS ( 
  SELECT * FROM t1 LEFT OUTER JOIN t3 
    ON (d = a) 
  WHERE b <= alias1.b OR e != alias2.c  
)
LIMIT_ROWS_EXAMINED 20

revision-id: timour@askmonty.org-20120213145248-tlvsd794o83odf2r
date: 2012-02-13 16:52:48 +0200
build-date: 2012-02-21 07:04:39 +0400
revno: 3417

id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1       PRIMARY alias1  ALL     NULL    NULL    NULL    NULL    3       100.00
1       PRIMARY alias2  ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Using join buffer (flat, BNL join)
2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    3       100.00
2       DEPENDENT SUBQUERY      t3      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Using join buffer (flat, BNL join)
Warnings:
Note    1276    Field or reference 'test.alias1.b' of SELECT #2 was resolved in SELECT #1
Note    1276    Field or reference 'test.alias2.c' of SELECT #2 was resolved in SELECT #1
Note    1003    select `test`.`alias1`.`a` AS `a`,`test`.`alias1`.`b` AS `b`,`test`.`alias2`.`c` AS `c` from `test`.`t1` `alias1` join `test`.`t2` `alias2` where (not(<expr_cache><`test`.`alias1`.`b`,`test`.`alias2`.`c`>(exists(select 1 from `test`.`t1` left join `test`.`t3` on((`test`.`t3`.`d` = `test`.`t1`.`a`)) where ((`test`.`t1`.`b` <= `test`.`alias1`.`b`) or (`test`.`t3`.`e` <> `test`.`alias2`.`c`))))))

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=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

CREATE TABLE t1 ( a INT, b INT );
INSERT INTO t1 VALUES 
  (3911,17),(3847,33),(3857,26);

CREATE TABLE t2 ( c VARCHAR(16) );
INSERT INTO t2 VALUES 
  ('English'),('French'),('German');

CREATE TABLE t3 ( d INT, e VARCHAR(32) );
INSERT INTO t3 VALUES 
  (3813,'United States'),(3814,'United States');

SELECT * FROM t1 AS alias1, t2 AS alias2 
WHERE NOT EXISTS ( 
  SELECT * FROM t1 LEFT OUTER JOIN t3 
    ON (d = a) 
  WHERE b <= alias1.b OR e != alias2.c  
);

SELECT * FROM t1 AS alias1, t2 AS alias2 
WHERE NOT EXISTS ( 
  SELECT * FROM t1 LEFT OUTER JOIN t3 
    ON (d = a) 
  WHERE b <= alias1.b OR e != alias2.c  
)
LIMIT_ROWS_EXAMINED 20;

SELECT * FROM t1 AS alias1, t2 AS alias2 
WHERE NOT EXISTS ( 
SELECT * FROM t1 LEFT OUTER JOIN t3 
ON (d = a) 
WHERE b <= alias1.b OR e != alias2.c  
);
a       b       c
SELECT * FROM t1 AS alias1, t2 AS alias2 
WHERE NOT EXISTS ( 
SELECT * FROM t1 LEFT OUTER JOIN t3 
ON (d = a) 
WHERE b <= alias1.b OR e != alias2.c  
)
LIMIT_ROWS_EXAMINED 20;
a       b       c
3847    33      English
3857    26      English
Warnings:
Warning 1930    Query execution was interrupted. The query examined at least 25 rows, which exceeds LIMIT_ROWS_EXAMINED. The query result may be incomplete.