diff options
| -rw-r--r-- | mysql-test/main/sort_nest.result | 58 | ||||
| -rw-r--r-- | mysql-test/main/sort_nest.test | 37 |
2 files changed, 95 insertions, 0 deletions
diff --git a/mysql-test/main/sort_nest.result b/mysql-test/main/sort_nest.result index 6ab2f3cd595..3c5c36fb141 100644 --- a/mysql-test/main/sort_nest.result +++ b/mysql-test/main/sort_nest.result @@ -2047,6 +2047,64 @@ a a b b 1 1 1 1 0 0 0 0 set @@optimizer_switch= @save_optimizer_switch; +# +# Semi join materialization lookup +# +EXPLAIN SELECT t1.a, t2.a, t1.b,t2.b +FROM t1, t2 +WHERE t1.a=t2.a AND +t1.b IN (SELECT t4.a FROM t3,t4 WHERE t4.a <= 5) +ORDER BY t2.b DESC, t1.b DESC +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL a NULL NULL NULL 10 Using where +1 PRIMARY t1 ref a a 5 test.t2.a 1 +1 PRIMARY <sort-nest> ALL NULL NULL NULL NULL 1 Using filesort +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t3 index NULL b 5 NULL 10 Using index +2 MATERIALIZED t4 index a a 5 NULL 10 Using where; Using index; Using join buffer (flat, BNL join) +SELECT t1.a, t2.a, t1.b,t2.b +FROM t1, t2 +WHERE t1.a=t2.a AND +t1.b IN (SELECT t4.a FROM t3,t4 WHERE t4.a <= 5) +ORDER BY t2.b DESC, t1.b DESC +LIMIT 5; +a a b b +4 4 4 4 +3 3 3 3 +2 2 2 2 +1 1 1 1 +0 0 0 0 +# +# Loosescan strategy +# +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch='materialization=off,firstmatch=off'; +ALTER TABLE t1 ADD KEY b(b); +EXPLAIN SELECT t1.a, t2.a, t1.b,t2.b +FROM t1, t2 +WHERE t1.a=t2.a AND +t1.b IN (SELECT t4.a FROM t4) +ORDER BY t2.a DESC, t1.b DESC +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t4 index a a 5 NULL 10 Using where; Using index; LooseScan +1 PRIMARY t1 ref a,b b 5 test.t4.a 1 +1 PRIMARY <sort-nest> ALL NULL NULL NULL NULL 5 Using filesort +1 PRIMARY t2 ref a a 5 sort-nest.a 1 +SELECT t1.a, t2.a, t1.b,t2.b +FROM t1, t2 +WHERE t1.a=t2.a AND +t1.b IN (SELECT t4.a FROM t4) +ORDER BY t2.a DESC, t1.b DESC +LIMIT 5; +a a b b +4 4 4 4 +3 3 3 3 +2 2 2 2 +1 1 1 1 +0 0 0 0 +set @@optimizer_switch= @save_optimizer_switch; drop table t0,t1,t2,t3,t4; # NON-MERGED SEMI JOINS diff --git a/mysql-test/main/sort_nest.test b/mysql-test/main/sort_nest.test index 5af7fbda7aa..f4392c4e3a0 100644 --- a/mysql-test/main/sort_nest.test +++ b/mysql-test/main/sort_nest.test @@ -968,6 +968,43 @@ eval EXPLAIN $query; eval $query; set @@optimizer_switch= @save_optimizer_switch; + + +--echo # +--echo # Semi join materialization lookup +--echo # + +let $query= +SELECT t1.a, t2.a, t1.b,t2.b +FROM t1, t2 + WHERE t1.a=t2.a AND + t1.b IN (SELECT t4.a FROM t3,t4 WHERE t4.a <= 5) +ORDER BY t2.b DESC, t1.b DESC +LIMIT 5; + +eval EXPLAIN $query; +eval $query; + +--echo # +--echo # Loosescan strategy +--echo # + +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch='materialization=off,firstmatch=off'; + +let $query= +SELECT t1.a, t2.a, t1.b,t2.b +FROM t1, t2 + WHERE t1.a=t2.a AND + t1.b IN (SELECT t4.a FROM t4) +ORDER BY t2.a DESC, t1.b DESC +LIMIT 5; + +ALTER TABLE t1 ADD KEY b(b); +eval EXPLAIN $query; +eval $query; +set @@optimizer_switch= @save_optimizer_switch; + drop table t0,t1,t2,t3,t4; --echo |