|
The following query
SELECT * FROM t1 AS alias1, t1 AS alias2
|
WHERE ( alias1.b, alias2.c ) NOT IN
|
( SELECT d, a FROM t1, t2 )
|
LIMIT ROWS_EXAMINED 17000
|
on the test data (500 rows in t1, 320 rows in t2) examines ~903K rows before being interrupted.
bzr version-info
revision-id: timour@askmonty.org-20120305200233-lyj8vveiuwkm1nq2
date: 2012-03-05 22:02:33 +0200
build-date: 2012-03-08 19:42:33 +0400
revno: 3453
EXPLAIN:
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY alias1 ALL NULL NULL NULL NULL 500 100.00
|
1 PRIMARY alias2 ALL NULL NULL NULL NULL 500 100.00 Using where; Using join buffer (flat, BNL join)
|
2 MATERIALIZED t2 index PRIMARY PRIMARY 4 NULL 320 100.00 Using index
|
2 MATERIALIZED t1 index PRIMARY PRIMARY 4 NULL 500 100.00 Using index; Using join buffer (flat, BNL join)
|
Warnings:
|
Note 1003 select `test`.`alias1`.`a` AS `a`,`test`.`alias1`.`b` AS `b`,`test`.`alias1`.`c` AS `c`,`test`.`alias2`.`a` AS `a`,`test`.`alias2`.`b` AS `b`,`test`.`alias2`.`c` AS `c` from `test`.`t1` `alias1` join `test`.`t1` `alias2` where (not(<expr_cache><`test`.`alias1`.`b`,`test`.`alias2`.`c`>(<in_optimizer>((`test`.`alias1`.`b`,`test`.`alias2`.`c`),(`test`.`alias1`.`b`,`test`.`alias2`.`c`) in ( <materialize> (select `test`.`t2`.`d`,`test`.`t1`.`a` from `test`.`t1` join `test`.`t2` ), <primary_index_lookup>(`test`.`alias1`.`b` in <temporary table> on distinct_key where ((`test`.`alias1`.`b` = `<subquery2>`.`d`) and (`test`.`alias2`.`c` = `<subquery2>`.`a`))))))))
|
Minimal optimizer_switch:
materialization=on,partial_match_rowid_merge=on,subquery_cache=on
|
Full optimizer_switch:
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
|
Switching off subquery_cache changes the number of examined rows a little, but does not eliminate the problem.
Test case:
|
SET optimizer_switch = 'materialization=on,partial_match_rowid_merge=on,subquery_cache=on';
|
|
CREATE TABLE t1 (
|
a INT AUTO_INCREMENT PRIMARY KEY,
|
b INT, c INT
|
);
|
|
# 500 rows
|
INSERT INTO t1 ( c, b ) VALUES
|
(132,209),(2,1),(1,4),(0,5),(0,7),(7,NULL),(3,NULL),(0,1),
|
(8,7),(NULL,NULL),(7,6),(0,4),(NULL,2),(2,3),(6,3),
|
(3,NULL),(7,8),(1,7),(6,0),(134,51),(7,6),(9,2),(0,8),
|
(5,NULL),(NULL,2),(NULL,6),(NULL,5),(9,4),(1,4),(NULL,6),
|
(6,0),(0,6),(1,2),(4,NULL),(1,NULL),(239,135),(148,18),
|
(2,6),(4,6),(6,8),(2,4),(9,0),(6,NULL),(9,3),(3,3),(8,5),
|
(1,5),(2,1),(6,8),(4,2),(0,6),(4,6),(1,3),(NULL,2),(9,4),
|
(1,2),(0,1),(147,34),(5,2),(5,4),(8,3),(6,NULL),(3,2),
|
(6,NULL),(9,2),(7,6),(80,227),(0,7),(9,NULL),(5,6),(0,2),
|
(9,7),(0,NULL),(4,6),(6,5),(1,3),(0,5),(9,6),(1,7),(7,0),
|
(9,6),(6,3),(3,4),(5,1),(3,4),(2,3),(NULL,8),(5,0),
|
(81,187),(2,2),(6,NULL),(5,3),(0,NULL),(9,8),(9,2),
|
(NULL,7),(8,6),(NULL,3),(8,4),(7,9),(4,8),(0,1),(NULL,2),
|
(NULL,6),(4,4),(0,0),(3,9),(3,2),(243,17),(2,0),(2,0),
|
(0,NULL),(9,9),(186,4),(4,NULL),(7,3),(8,2),(7,6),(181,15),
|
(115,46),(9,1),(NULL,7),(9,2),(NULL,6),(5,3),(3,9),(9,4),
|
(NULL,8),(130,129),(6,2),(2,8),(5,6),(3,4),(0,NULL),(0,4),
|
(1,6),(NULL,5),(7,6),(NULL,9),(3,NULL),(2,5),(2,2),
|
(8,NULL),(5,1),(2,9),(1,9),(0,3),(2,4),(8,7),(NULL,6),
|
(0,8),(NULL,7),(7,1),(6,4),(8,8),(5,5),(8,5),(7,1),(0,8),
|
(3,4),(2,5),(157,54),(0,5),(NULL,2),(5,7),(5,1),(7,2),
|
(9,7),(212,78),(7,4),(NULL,0),(6,1),(1,0),(7,6),(6,7),
|
(8,4),(0,2),(9,3),(8,1),(4,4),(6,9),(7,3),(132,59),
|
(1,NULL),(0,5),(1,1),(4,2),(2,0),(2,5),(4,6),(5,4),(7,0),
|
(221,163),(2,9),(NULL,5),(7,NULL),(3,7),(9,NULL),(NULL,2),
|
(45,127),(1,2),(2,6),(8,7),(8,5),(NULL,4),(3,2),(7,5),
|
(4,7),(4,2),(130,35),(3,0),(4,1),(1,6),(6,8),(9,2),(8,3),
|
(9,7),(7,NULL),(6,1),(5,8),(3,4),(9,5),(4,0),(NULL,6),
|
(6,9),(8,5),(1,0),(3,9),(1,1),(NULL,7),(8,9),(3,NULL),
|
(1,2),(6,5),(3,5),(6,NULL),(3,7),(0,8),(4,9),(7,5),(6,5),
|
(0,5),(2,3),(4,1),(0,4),(2,NULL),(4,1),(0,5),(0,5),(2,9),
|
(5,6),(224,112),(9,5),(5,1),(6,NULL),(3,0),(2,2),(3,2),
|
(3,5),(2,6),(6,4),(4,9),(NULL,9),(20,132),(8,8),(7,0),
|
(6,5),(2,4),(104,101),(2,6),(7,2),(2,NULL),(7,6),(3,9),
|
(8,9),(2,7),(0,NULL),(2,2),(3,6),(2,5),(2,6),(NULL,3),
|
(6,6),(9,1),(21,NULL),(3,NULL),(1,3),(0,NULL),(9,7),(4,5),
|
(6,1),(7,4),(NULL,6),(8,3),(180,89),(NULL,9),(8,NULL),
|
(6,5),(2,0),(6,2),(3,0),(5,8),(2,1),(NULL,NULL),(0,NULL),
|
(6,4),(4,4),(2,3),(9,2),(2,NULL),(5,0),(7,4),(0,8),(5,6),
|
(8,4),(8,5),(6,6),(7,2),(243,71),(7,4),(4,1),(250,100),
|
(1,4),(NULL,NULL),(5,9),(8,0),(2,2),(8,4),(5,NULL),
|
(NULL,8),(NULL,NULL),(7,5),(2,0),(8,8),(5,8),(0,8),(9,3),
|
(9,7),(1,6),(1,3),(1,4),(79,137),(3,5),(6,5),(130,29),
|
(5,7),(5,3),(9,NULL),(9,8),(7,3),(8,7),(9,9),(9,NULL),
|
(7,9),(8,9),(0,8),(1,2),(6,6),(0,8),(8,2),(NULL,7),
|
(NULL,0),(2,2),(8,2),(9,0),(8,5),(6,8),(7,NULL),(NULL,2),
|
(2,1),(1,8),(5,2),(NULL,2),(NULL,3),(9,9),(5,1),(9,5),
|
(NULL,5),(NULL,6),(9,123),(5,0),(0,6),(NULL,0),(98,75),
|
(5,9),(242,69),(7,8),(1,NULL),(25,51),(4,8),(8,5),(9,5),
|
(NULL,3),(3,NULL),(5,NULL),(4,4),(6,8),(3,5),(NULL,5),
|
(0,3),(4,8),(9,1),(1,9),(4,9),(3,5),(4,NULL),(0,NULL),
|
(0,7),(4,5),(0,2),(9,8),(7,NULL),(152,245),(4,NULL),(7,7),
|
(6,NULL),(NULL,9),(8,NULL),(5,6),(3,5),(3,3),(3,6),(4,3),
|
(4,4),(5,2),(5,5),(9,4),(6,6),(5,4),(2,5),(5,5),(63,44),
|
(4,9),(9,5),(2,6),(3,6),(5,3),(1,4),(5,0),(2,7),(1,8),
|
(4,8),(2,1),(8,7),(NULL,NULL),(5,4),(4,9),(7,6),(9,4),
|
(4,NULL),(4,3),(4,2),(6,7),(5,8),(2,8),(7,4),(NULL,9),
|
(7,3),(32,51),(7,7),(8,7),(8,NULL),(7,8),(1,1),(7,9),(9,4),
|
(NULL,3),(1,2),(9,NULL),(2,2),(9,NULL),(2,6),(4,7),(0,2),
|
(4,5),(8,7),(NULL,6),(NULL,6),(0,2),(NULL,9),(8,6),(8,0),
|
(6,3),(2,8),(3,8),(1,6),(NULL,0),(1,NULL),(10,158),(2,5),
|
(103,163),(3,2),(6,8),(2,0),(7,9),(2,3),(2,1),(4,9),
|
(6,NULL),(6,5);
|
|
CREATE TABLE t2 ( d INT AUTO_INCREMENT PRIMARY KEY );
|
|
# 320 rows
|
INSERT INTO t2 VALUES
|
(NULL),(NULL),(NULL),(NULL),(NULL),
|
(NULL),(NULL),(NULL),(NULL),(NULL);
|
INSERT INTO t2 SELECT NULL FROM t2;
|
INSERT INTO t2 SELECT NULL FROM t2;
|
INSERT INTO t2 SELECT NULL FROM t2;
|
INSERT INTO t2 SELECT NULL FROM t2;
|
INSERT INTO t2 SELECT NULL FROM t2;
|
|
SELECT * FROM t1 AS alias1, t1 AS alias2
|
WHERE ( alias1.b, alias2.c ) NOT IN
|
( SELECT d, a FROM t1, t2 )
|
LIMIT ROWS_EXAMINED 17000;
|
|