LIMIT_ROWS_EXAMINED: query with OUTER JOIN in view or derived table, NOT IN examines 5 times more rows than the limit allows: limit ~ 30K, examined ~150K

SELECT * FROM v, t2

WHERE ( c, c ) NOT IN ( SELECT c, d FROM t2, t3 )

LIMIT_ROWS_EXAMINED 30000;

CREATE VIEW v AS

  SELECT alias1.*

  FROM t1 AS alias1 LEFT OUTER JOIN t1 AS alias2

    ON alias1.a = alias2.b;

Handler_read_key        1

Handler_read_rnd        143742

Handler_read_rnd_next   3461

Handler_tmp_write       3289

id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered    Extra

1       PRIMARY alias1  system  NULL    NULL    NULL    NULL    1       100.00

1       PRIMARY alias2  system  NULL    NULL    NULL    NULL    1       100.00

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

2       MATERIALIZED    t3      ALL     NULL    NULL    NULL    NULL    12      100.00

2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    274     100.00  Using join buffer (flat, BNL join)

Warnings:

Note    1003    select 'USAEnglish' AS `a`,86 AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` `alias1` left join `test`.`t1` `alias2` on(('USAEnglish' = NULL)) join `test`.`t2` where (not(<expr_cache><`test`.`t2`.`c`>(<in_optimizer>((`test`.`t2`.`c`,`test`.`t2`.`c`),(`test`.`t2`.`c`,`test`.`t2`.`c`) in ( <materialize> (select `test`.`t2`.`c`,`test`.`t3`.`d` from `test`.`t2` join `test`.`t3` ), <primary_index_lookup>(`test`.`t2`.`c` in <temporary table> on distinct_key where ((`test`.`t2`.`c` = `<subquery2>`.`c`) and (`test`.`t2`.`c` = `<subquery2>`.`d`))))))))

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

revision-id: timour@askmonty.org-20120213145248-tlvsd794o83odf2r

date: 2012-02-13 16:52:48 +0200

build-date: 2012-02-15 05:49:21 +0400

revno: 3417