mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-08-11 21:11:35 +02:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/main/opt_hints_join_cache.result
Oleg Smirnov 453a86f68e MDEV-36133 BNL() hint doesn't work with join_cache_level>=5 
This commit:
- fixes a couple of bugs in check_join_cache_usage();
- separates a part of opt_hints.test to a new file opt_hints_join_cache.test;
- add a batch of test cases run against different join_cache_level settings.
2025-05-05 12:02:47 +07:00

2045 lines
102 KiB
Text
Raw Permalink Blame History

set @tmp_jcl= @@join_cache_level;
set @tmp_opt= @@optimizer_switch;
CREATE TABLE t10(a INT);
INSERT INTO t10 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE t12(a INT, b INT);
INSERT INTO t12 SELECT a,a from t10;
CREATE TABLE t13(a INT, b INT, c INT, filler CHAR(100), key (a,b));
INSERT INTO t13 select a,a,a, 'filler-data' FROM t10;
================================================================
set optimizer_switch= 'join_cache_hashed=on,join_cache_bka=on,mrr=on';
***********************************************
set join_cache_level = 0;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
***********************************************
set join_cache_level = 1;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
***********************************************
set join_cache_level = 2;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
***********************************************
set join_cache_level = 3;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
***********************************************
set join_cache_level = 4;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
***********************************************
set join_cache_level = 5;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
***********************************************
set join_cache_level = 6;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
***********************************************
set join_cache_level = 7;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
***********************************************
set join_cache_level = 8;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
================================================================
set optimizer_switch= 'join_cache_hashed=off,join_cache_bka=on,mrr=on';
***********************************************
set join_cache_level = 0;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
***********************************************
set join_cache_level = 1;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
***********************************************
set join_cache_level = 2;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
***********************************************
set join_cache_level = 3;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
***********************************************
set join_cache_level = 4;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
***********************************************
set join_cache_level = 5;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
***********************************************
set join_cache_level = 6;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
set join_cache_level = 7;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
set join_cache_level = 8;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
================================================================
set optimizer_switch= 'join_cache_hashed=on,join_cache_bka=off,mrr=on';
***********************************************
set join_cache_level = 0;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
***********************************************
set join_cache_level = 1;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
***********************************************
set join_cache_level = 2;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
***********************************************
set join_cache_level = 3;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
***********************************************
set join_cache_level = 4;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
***********************************************
set join_cache_level = 5;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
***********************************************
set join_cache_level = 6;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
set join_cache_level = 7;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
set join_cache_level = 8;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join)
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
================================================================
set optimizer_switch= 'join_cache_hashed=off,join_cache_bka=off,mrr=on';
***********************************************
set join_cache_level = 0;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
***********************************************
set join_cache_level = 1;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
***********************************************
set join_cache_level = 2;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
set join_cache_level = 3;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
***********************************************
set join_cache_level = 4;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
***********************************************
set join_cache_level = 5;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
set join_cache_level = 6;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
***********************************************
set join_cache_level = 7;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
***********************************************
set join_cache_level = 8;
EXPLAIN SELECT * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
--------------------------------------------
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
--------------------------------------------
EXPLAIN SELECT /*+ NO_BKA() NO_BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition
drop tables t10, t12, t13;
================================================================
set optimizer_switch= 'join_cache_hashed=on,join_cache_bka=on,mrr=on';
#
# BNL and NO_BNL hint testing
#
set optimizer_switch=default;
CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 VALUES (1,1),(2,2);
CREATE TABLE t2 (a INT, b INT);
INSERT INTO t2 VALUES (1,1),(2,2);
CREATE TABLE t3 (a INT, b INT);
INSERT INTO t3 VALUES (1,1),(2,2);
# Check statistics without hint
FLUSH STATUS;
SELECT t1.* FROM t1,t2,t3;
a b
1 1
2 2
1 1
2 2
1 1
2 2
1 1
2 2
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 0
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
Handler_read_rnd_next 9
# Check statistics with hint
FLUSH STATUS;
SELECT /*+ NO_BNL() */t1.* FROM t1,t2,t3;
a b
1 1
1 1
1 1
1 1
2 2
2 2
2 2
2 2
SHOW STATUS LIKE 'handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 0
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 0
Handler_read_rnd_deleted 0
Handler_read_rnd_next 21
EXPLAIN EXTENDED SELECT t1.* FROM t1,t2,t3;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join)
1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (incremental, BNL join)
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t3`
EXPLAIN EXTENDED SELECT /*+ NO_BNL() */t1.* FROM t1,t2,t3;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00
1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00
Warnings:
Note 1003 select /*+ NO_BNL(@`select#1`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t3`
EXPLAIN EXTENDED SELECT /*+ NO_BNL(t2, t3) */t1.* FROM t1,t2,t3;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00
1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00
Warnings:
Note 1003 select /*+ NO_BNL(`t2`@`select#1`) NO_BNL(`t3`@`select#1`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t3`
EXPLAIN EXTENDED SELECT /*+ NO_BNL(t1, t3) */t1.* FROM t1,t2,t3;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join)
1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00
Warnings:
Note 1003 select /*+ NO_BNL(`t1`@`select#1`) NO_BNL(`t3`@`select#1`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t3`
# Make sure query results are the same for any hints combination
SELECT * FROM t1,t2,t3;
a b a b a b
1 1 1 1 1 1
1 1 1 1 2 2
1 1 2 2 1 1
1 1 2 2 2 2
2 2 1 1 1 1
2 2 1 1 2 2
2 2 2 2 1 1
2 2 2 2 2 2
SELECT /*+ BNL() */t1.* FROM t1,t2,t3;
a b
1 1
1 1
1 1
1 1
2 2
2 2
2 2
2 2
SELECT /*+ BNL(t1, t2) */t1.* FROM t1,t2,t3;
a b
1 1
1 1
1 1
1 1
2 2
2 2
2 2
2 2
SELECT /*+ BNL(t1, t3) */t1.* FROM t1,t2,t3;
a b
1 1
1 1
1 1
1 1
2 2
2 2
2 2
2 2
SELECT /*+ BNL(t2, t3) */t1.* FROM t1,t2,t3;
a b
1 1
1 1
1 1
1 1
2 2
2 2
2 2
2 2
SELECT /*+ BNL(t2) BNL(t3) */t1.* FROM t1,t2,t3;
a b
1 1
1 1
1 1
1 1
2 2
2 2
2 2
2 2
SELECT /*+ NO_BNL() */* FROM t1,t2,t3;
a b a b a b
1 1 1 1 1 1
1 1 1 1 2 2
1 1 2 2 1 1
1 1 2 2 2 2
2 2 1 1 1 1
2 2 1 1 2 2
2 2 2 2 1 1
2 2 2 2 2 2
SELECT /*+ NO_BNL(t1, t2) */* FROM t1,t2,t3;
a b a b a b
1 1 1 1 1 1
1 1 1 1 2 2
1 1 2 2 1 1
1 1 2 2 2 2
2 2 1 1 1 1
2 2 1 1 2 2
2 2 2 2 1 1
2 2 2 2 2 2
SELECT /*+ NO_BNL(t1, t3) */* FROM t1,t2,t3;
a b a b a b
1 1 1 1 1 1
1 1 1 1 2 2
1 1 2 2 1 1
1 1 2 2 2 2
2 2 1 1 1 1
2 2 1 1 2 2
2 2 2 2 1 1
2 2 2 2 2 2
SELECT /*+ NO_BNL(t2, t3) */* FROM t1,t2,t3;
a b a b a b
1 1 1 1 1 1
1 1 1 1 2 2
1 1 2 2 1 1
1 1 2 2 2 2
2 2 1 1 1 1
2 2 1 1 2 2
2 2 2 2 1 1
2 2 2 2 2 2
# BNL() overrides current join_cache_level setting
set join_cache_level = 0;
EXPLAIN EXTENDED SELECT t1.* FROM t1,t2,t3;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00
1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t3`
EXPLAIN EXTENDED SELECT /*+ BNL() */t1.* FROM t1,t2,t3;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join)
1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (incremental, BNL join)
Warnings:
Note 1003 select /*+ BNL(@`select#1`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t3`
EXPLAIN EXTENDED SELECT /*+ BNL(t1, t2) */t1.* FROM t1,t2,t3;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join)
1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00
Warnings:
Note 1003 select /*+ BNL(`t1`@`select#1`) BNL(`t2`@`select#1`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t3`
EXPLAIN EXTENDED SELECT /*+ BNL(t1, t3) */t1.* FROM t1,t2,t3;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00
1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join)
Warnings:
Note 1003 select /*+ BNL(`t1`@`select#1`) BNL(`t3`@`select#1`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t3`
EXPLAIN EXTENDED SELECT /*+ BNL(t2, t3) */t1.* FROM t1,t2,t3;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join)
1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (incremental, BNL join)
Warnings:
Note 1003 select /*+ BNL(`t2`@`select#1`) BNL(`t3`@`select#1`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t3`
EXPLAIN EXTENDED SELECT /*+ BNL(t2) BNL(t3) */t1.* FROM t1,t2,t3;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join)
1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (incremental, BNL join)
Warnings:
Note 1003 select /*+ BNL(`t2`@`select#1`) BNL(`t3`@`select#1`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t3`
DROP TABLE t1, t2, t3;
set join_cache_level = 8;
# BNL in subquery
set optimizer_switch = DEFAULT;
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a));
CREATE TABLE t2 (a INT);
CREATE TABLE t3 (a INT, b INT, INDEX a (a,b));
INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40);
INSERT INTO t2 VALUES (2), (3), (4), (5);
INSERT INTO t3 VALUES (10,3), (20,4), (30,5);
ANALYZE TABLE t1, t2, t3;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
test.t2 analyze status Engine-independent statistics collected
test.t2 analyze status OK
test.t3 analyze status Engine-independent statistics collected
test.t3 analyze status OK
EXPLAIN EXTENDED SELECT /*+ QB_NAME(q) */ * FROM t1 JOIN t2 ON t1.a = t2.a WHERE
t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.a = t4.b);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00
1 PRIMARY t2 hash_ALL NULL #hash#$hj 5 test.t1.a 4 10.00 Using where; Using join buffer (flat, BNLH join)
1 PRIMARY t3 index a a 10 NULL 3 10.00 Using where; Using index; Using join buffer (incremental, BNL join)
1 PRIMARY t4 ALL NULL NULL NULL NULL 4 25.00 Using where; FirstMatch(t2); Using join buffer (incremental, BNL join)
Warnings:
Note 1003 select /*+ QB_NAME(`q`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a` from `test`.`t1` semi join (`test`.`t3` join `test`.`t1` `t4`) join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a` and `test`.`t3`.`b` = `test`.`t1`.`a` and `test`.`t4`.`b` = `test`.`t3`.`a`
EXPLAIN EXTENDED SELECT /*+ QB_NAME(q) NO_BNL() */ *
FROM t1 JOIN t2 ON t1.a = t2.a
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.a = t4.b);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00
1 PRIMARY t2 ALL NULL NULL NULL NULL 4 10.00 Using where
1 PRIMARY t3 index a a 10 NULL 3 10.00 Using where; Using index; Using join buffer (flat, BNL join)
1 PRIMARY t4 ALL NULL NULL NULL NULL 4 25.00 Using where; FirstMatch(t2); Using join buffer (incremental, BNL join)
Warnings:
Note 1003 select /*+ QB_NAME(`q`) NO_BNL(@`q`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a` from `test`.`t1` semi join (`test`.`t3` join `test`.`t1` `t4`) join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a` and `test`.`t3`.`b` = `test`.`t1`.`a` and `test`.`t4`.`b` = `test`.`t3`.`a`
EXPLAIN EXTENDED SELECT /*+ QB_NAME(q) NO_BNL(t1, t2) */ *
FROM t1 JOIN t2 ON t1.a = t2.a
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.a = t4.b);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00
1 PRIMARY t2 ALL NULL NULL NULL NULL 4 10.00 Using where
1 PRIMARY t3 index a a 10 NULL 3 10.00 Using where; Using index; Using join buffer (flat, BNL join)
1 PRIMARY t4 ALL NULL NULL NULL NULL 4 25.00 Using where; FirstMatch(t2); Using join buffer (incremental, BNL join)
Warnings:
Note 1003 select /*+ QB_NAME(`q`) NO_BNL(`t1`@`q`) NO_BNL(`t2`@`q`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a` from `test`.`t1` semi join (`test`.`t3` join `test`.`t1` `t4`) join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a` and `test`.`t3`.`b` = `test`.`t1`.`a` and `test`.`t4`.`b` = `test`.`t3`.`a`
EXPLAIN EXTENDED SELECT /*+ QB_NAME(q) NO_BNL(@subq1) */ *
FROM t1 JOIN t2 ON t1.a = t2.a
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.a = t4.b);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00
1 PRIMARY t2 hash_ALL NULL #hash#$hj 5 test.t1.a 4 10.00 Using where; Using join buffer (flat, BNLH join)
1 PRIMARY t3 index a a 10 NULL 3 10.00 Using where; Using index
1 PRIMARY t4 ALL NULL NULL NULL NULL 4 25.00 Using where; FirstMatch(t2)
Warnings:
Note 1003 select /*+ QB_NAME(`q`) NO_BNL(@`subq1`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a` from `test`.`t1` semi join (`test`.`t3` join `test`.`t1` `t4`) join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a` and `test`.`t3`.`b` = `test`.`t1`.`a` and `test`.`t4`.`b` = `test`.`t3`.`a`
EXPLAIN EXTENDED SELECT /*+ QB_NAME(q) NO_BNL(t4@subq1) */ *
FROM t1 JOIN t2 ON t1.a = t2.a
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.a = t4.b);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00
1 PRIMARY t2 hash_ALL NULL #hash#$hj 5 test.t1.a 4 10.00 Using where; Using join buffer (flat, BNLH join)
1 PRIMARY t3 index a a 10 NULL 3 10.00 Using where; Using index
1 PRIMARY t4 ALL NULL NULL NULL NULL 4 25.00 Using where; FirstMatch(t2)
Warnings:
Note 1003 select /*+ QB_NAME(`q`) NO_BNL(`t4`@`subq1`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a` from `test`.`t1` semi join (`test`.`t3` join `test`.`t1` `t4`) join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a` and `test`.`t3`.`b` = `test`.`t1`.`a` and `test`.`t4`.`b` = `test`.`t3`.`a`
EXPLAIN EXTENDED SELECT /*+ QB_NAME(q) NO_BNL(t3@subq1,t4@subq1) */ *
FROM t1 JOIN t2 ON t1.a = t2.a
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.a = t4.b);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00
1 PRIMARY t2 hash_ALL NULL #hash#$hj 5 test.t1.a 4 10.00 Using where; Using join buffer (flat, BNLH join)
1 PRIMARY t3 index a a 10 NULL 3 10.00 Using where; Using index
1 PRIMARY t4 ALL NULL NULL NULL NULL 4 25.00 Using where; FirstMatch(t2)
Warnings:
Note 1003 select /*+ QB_NAME(`q`) NO_BNL(`t3`@`subq1`) NO_BNL(`t4`@`subq1`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a` from `test`.`t1` semi join (`test`.`t3` join `test`.`t1` `t4`) join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a` and `test`.`t3`.`b` = `test`.`t1`.`a` and `test`.`t4`.`b` = `test`.`t3`.`a`
EXPLAIN EXTENDED SELECT /*+ QB_NAME(q) NO_BNL(@subq1 t3, t4) */ *
FROM t1 JOIN t2 ON t1.a = t2.a
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.a = t4.b);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00
1 PRIMARY t2 hash_ALL NULL #hash#$hj 5 test.t1.a 4 10.00 Using where; Using join buffer (flat, BNLH join)
1 PRIMARY t3 index a a 10 NULL 3 10.00 Using where; Using index
1 PRIMARY t4 ALL NULL NULL NULL NULL 4 25.00 Using where; FirstMatch(t2)
Warnings:
Note 1003 select /*+ QB_NAME(`q`) NO_BNL(`t3`@`subq1`) NO_BNL(`t4`@`subq1`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a` from `test`.`t1` semi join (`test`.`t3` join `test`.`t1` `t4`) join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a` and `test`.`t3`.`b` = `test`.`t1`.`a` and `test`.`t4`.`b` = `test`.`t3`.`a`
EXPLAIN EXTENDED SELECT /*+ QB_NAME(q) */ *
FROM t1 JOIN t2 ON t1.a = t2.a
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) NO_BNL(t3, t4) */ t3.b
FROM t3 JOIN t1 t4 ON t3.a = t4.b);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00
1 PRIMARY t2 hash_ALL NULL #hash#$hj 5 test.t1.a 4 10.00 Using where; Using join buffer (flat, BNLH join)
1 PRIMARY t3 index a a 10 NULL 3 10.00 Using where; Using index
1 PRIMARY t4 ALL NULL NULL NULL NULL 4 25.00 Using where; FirstMatch(t2)
Warnings:
Note 1003 select /*+ QB_NAME(`q`) NO_BNL(`t3`@`subq1`) NO_BNL(`t4`@`subq1`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a` from `test`.`t1` semi join (`test`.`t3` join `test`.`t1` `t4`) join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a` and `test`.`t3`.`b` = `test`.`t1`.`a` and `test`.`t4`.`b` = `test`.`t3`.`a`
# Make sure query results are the same for any hints combination
SELECT * FROM t1 JOIN t2 ON t1.a = t2.a WHERE
t2.a IN (SELECT t3.b FROM t3 JOIN t1 t4 ON t3.a = t4.b);
a b a
3 30 3
4 40 4
SELECT /*+ QB_NAME(q) */ * FROM t1 JOIN t2 ON t1.a = t2.a WHERE
t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.a = t4.b);
a b a
3 30 3
4 40 4
SELECT /*+ QB_NAME(q) NO_BNL() */ *
FROM t1 JOIN t2 ON t1.a = t2.a
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.a = t4.b);
a b a
3 30 3
4 40 4
SELECT /*+ QB_NAME(q) NO_BNL(t1, t2) */ *
FROM t1 JOIN t2 ON t1.a = t2.a
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.a = t4.b);
a b a
3 30 3
4 40 4
SELECT /*+ QB_NAME(q) NO_BNL(@subq1) */ *
FROM t1 JOIN t2 ON t1.a = t2.a
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.a = t4.b);
a b a
3 30 3
4 40 4
SELECT /*+ QB_NAME(q) NO_BNL(t4@subq1) */ *
FROM t1 JOIN t2 ON t1.a = t2.a
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.a = t4.b);
a b a
3 30 3
4 40 4
SELECT /*+ QB_NAME(q) NO_BNL(t3@subq1,t4@subq1) */ *
FROM t1 JOIN t2 ON t1.a = t2.a
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.a = t4.b);
a b a
3 30 3
4 40 4
SELECT /*+ QB_NAME(q) NO_BNL(@subq1 t3, t4) */ *
FROM t1 JOIN t2 ON t1.a = t2.a
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.a = t4.b);
a b a
3 30 3
4 40 4
SELECT /*+ QB_NAME(q) */ *
FROM t1 JOIN t2 ON t1.a = t2.a
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) NO_BNL(t3, t4) */ t3.b
FROM t3 JOIN t1 t4 ON t3.a = t4.b);
a b a
3 30 3
4 40 4
DROP TABLE t1, t2, t3;
#
# BNL() and NO_BNL() for LEFT JOINs
#
CREATE TABLE t1 (a int);
CREATE TABLE t2 (a int);
CREATE TABLE t3 (a int);
CREATE TABLE t4 (a int);
INSERT INTO t1 VALUES (null), (2), (null), (1);
set join_cache_level = 8;
# Two join buffers are employed by default (without hints):
EXPLAIN SELECT t1.a
FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a) ON 0
WHERE t1.a OR t3.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 4
1 SIMPLE t2 ALL NULL NULL NULL NULL 0 Using where; Using join buffer (flat, BNL join)
1 SIMPLE t3 ALL NULL NULL NULL NULL 0 Using where; Using join buffer (incremental, BNL join)
# Disabling either of join buffers disables another one automatically due
# to join buffer employment rules:
EXPLAIN SELECT /*+ NO_BNL(t2) */ t1.a
FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a) ON 0
WHERE t1.a OR t3.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 4
1 SIMPLE t2 ALL NULL NULL NULL NULL 0 Using where
1 SIMPLE t3 ALL NULL NULL NULL NULL 0 Using where
EXPLAIN SELECT /*+ NO_BNL(t3) */ t1.a
FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a) ON 0
WHERE t1.a OR t3.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 4
1 SIMPLE t2 ALL NULL NULL NULL NULL 0 Using where
1 SIMPLE t3 ALL NULL NULL NULL NULL 0 Using where
# Three join buffers are employed here by default (without hints):
EXPLAIN SELECT t1.a
FROM t1 LEFT JOIN (t2 LEFT JOIN (t3 LEFT JOIN t4 ON 1) ON t2.a) ON 0
WHERE t1.a OR t4.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 4
1 SIMPLE t2 ALL NULL NULL NULL NULL 0 Using where; Using join buffer (flat, BNL join)
1 SIMPLE t3 ALL NULL NULL NULL NULL 0 Using where; Using join buffer (incremental, BNL join)
1 SIMPLE t4 ALL NULL NULL NULL NULL 0 Using where; Using join buffer (incremental, BNL join)
# Disabling either of join buffers disables others automatically due
# to join buffer employment rules:
EXPLAIN SELECT /*+ NO_BNL(t2)*/ t1.a
FROM t1 LEFT JOIN (t2 LEFT JOIN (t3 LEFT JOIN t4 ON 1) ON t2.a) ON 0
WHERE t1.a OR t4.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 4
1 SIMPLE t2 ALL NULL NULL NULL NULL 0 Using where
1 SIMPLE t3 ALL NULL NULL NULL NULL 0 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 0 Using where
EXPLAIN SELECT /*+ NO_BNL(t3)*/ t1.a
FROM t1 LEFT JOIN (t2 LEFT JOIN (t3 LEFT JOIN t4 ON 1) ON t2.a) ON 0
WHERE t1.a OR t4.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 4
1 SIMPLE t2 ALL NULL NULL NULL NULL 0 Using where
1 SIMPLE t3 ALL NULL NULL NULL NULL 0 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 0 Using where
EXPLAIN SELECT /*+ NO_BNL(t4)*/ t1.a
FROM t1 LEFT JOIN (t2 LEFT JOIN (t3 LEFT JOIN t4 ON 1) ON t2.a) ON 0
WHERE t1.a OR t4.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 4
1 SIMPLE t2 ALL NULL NULL NULL NULL 0 Using where
1 SIMPLE t3 ALL NULL NULL NULL NULL 0 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 0 Using where
set join_cache_level=0;
# It is not allowed to enable join buffers on some subset of inner tables
# of an outer join. Either all tables should use join buffers or none:
EXPLAIN SELECT /*+ BNL(t2)*/ t1.a
FROM t1 LEFT JOIN (t2 LEFT JOIN (t3 LEFT JOIN t4 ON 1) ON t2.a) ON 0
WHERE t1.a OR t4.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 4
1 SIMPLE t2 ALL NULL NULL NULL NULL 0 Using where
1 SIMPLE t3 ALL NULL NULL NULL NULL 0 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 0 Using where
EXPLAIN SELECT /*+ BNL(t2, t3)*/ t1.a
FROM t1 LEFT JOIN (t2 LEFT JOIN (t3 LEFT JOIN t4 ON 1) ON t2.a) ON 0
WHERE t1.a OR t4.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 4
1 SIMPLE t2 ALL NULL NULL NULL NULL 0 Using where
1 SIMPLE t3 ALL NULL NULL NULL NULL 0 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 0 Using where
EXPLAIN SELECT /*+ BNL(t2, t3, t4)*/ t1.a
FROM t1 LEFT JOIN (t2 LEFT JOIN (t3 LEFT JOIN t4 ON 1) ON t2.a) ON 0
WHERE t1.a OR t4.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 4
1 SIMPLE t2 ALL NULL NULL NULL NULL 0 Using where; Using join buffer (flat, BNL join)
1 SIMPLE t3 ALL NULL NULL NULL NULL 0 Using where; Using join buffer (incremental, BNL join)
1 SIMPLE t4 ALL NULL NULL NULL NULL 0 Using where; Using join buffer (incremental, BNL join)
set join_cache_level=8;
INSERT INTO t2 VALUES (1), (2), (null), (1);
# BNLH buffer is used when allowed by JCL setting
EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where
1 SIMPLE t2 hash_ALL NULL #hash#$hj 5 test.t1.a 4 Using where; Using join buffer (flat, BNLH join)
# Make sure the hint enables BNL even when JCL=0:
set join_cache_level=0;
EXPLAIN SELECT /*+BNL(t2) */ * FROM t1, t2 WHERE t1.a=t2.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where
1 SIMPLE t2 hash_ALL NULL #hash#$hj 5 test.t1.a 4 Using where; Using join buffer (flat, BNLH join)
set join_cache_level=2;
EXPLAIN SELECT /*+BNL(t2) */ * FROM t1, t2 WHERE t1.a=t2.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where
1 SIMPLE t2 hash_ALL NULL #hash#$hj 5 test.t1.a 4 Using where; Using join buffer (flat, BNLH join)
DROP TABLE t1,t2,t3,t4;
#
# Mix of BNL/BKA flat and incremental join buffers for OUTER JOINs
#
set optimizer_switch='outer_join_with_cache=on,mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
CREATE TABLE t1 (a1 varchar(32)) ;
INSERT INTO t1 VALUES ('s'),('k');
CREATE TABLE t2 (a2 int PRIMARY KEY, b2 varchar(32)) ;
INSERT INTO t2 VALUES (7,'s');
CREATE TABLE t3 (a3 int PRIMARY KEY, b3 varchar(32)) ;
INSERT INTO t3 VALUES (7,'s');
CREATE TABLE t4 (a4 int);
INSERT INTO t4 VALUES (9);
CREATE TABLE t5(a5 int PRIMARY KEY, b5 int) ;
INSERT INTO t5 VALUES (7,0);
ANALYZE TABLES t1, t2, t3, t4, t5;
# Disable join buffering to enable it selectively on particular tables
SET SESSION join_cache_level = 0;
EXPLAIN
SELECT t4.a4, t5.b5
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
1 SIMPLE t3 index PRIMARY PRIMARY 4 NULL 1 Using where; Using index
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t3.a3 1 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 1 Using where
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t2.a2 1 Using where
SELECT t4.a4, t5.b5
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
a4 b5
9 0
9 NULL
EXPLAIN
SELECT /*+ BNL(t4) */ t4.a4, t5.b5
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
1 SIMPLE t3 index PRIMARY PRIMARY 4 NULL 1 Using where; Using index
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t3.a3 1 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (flat, BNL join)
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t2.a2 1 Using where
SELECT /*+ BNL(t4) */ t4.a4, t5.b5
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
a4 b5
9 0
9 NULL
EXPLAIN
SELECT /*+ BNL(t4, t5) */ t4.a4, t5.b5
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
1 SIMPLE t3 index PRIMARY PRIMARY 4 NULL 1 Using where; Using index
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t3.a3 1 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (flat, BNL join)
1 SIMPLE t5 hash_ALL PRIMARY #hash#PRIMARY 4 test.t2.a2 1 Using where; Using join buffer (incremental, BNLH join)
SELECT /*+ BNL(t4, t5) */ t4.a4, t5.b5
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
a4 b5
9 0
9 NULL
# BNL() hint overrides join_cache_levels from 0 to 3 increasing it to 4
set join_cache_level = 1;
EXPLAIN
SELECT /*+ BNL(t4, t5) */ t4.a4, t5.b5
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
1 SIMPLE t3 index PRIMARY PRIMARY 4 NULL 1 Using where; Using index
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t3.a3 1 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (flat, BNL join)
1 SIMPLE t5 hash_ALL PRIMARY #hash#PRIMARY 4 test.t2.a2 1 Using where; Using join buffer (incremental, BNLH join)
set join_cache_level = 2;
EXPLAIN
SELECT /*+ BNL(t4, t5) */ t4.a4, t5.b5
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
1 SIMPLE t3 index PRIMARY PRIMARY 4 NULL 1 Using where; Using index
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t3.a3 1 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (flat, BNL join)
1 SIMPLE t5 hash_ALL PRIMARY #hash#PRIMARY 4 test.t2.a2 1 Using where; Using join buffer (incremental, BNLH join)
set join_cache_level = 3;
EXPLAIN
SELECT /*+ BNL(t4, t5) */ t4.a4, t5.b5
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.a2 1 Using index
1 SIMPLE t4 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (flat, BNL join)
1 SIMPLE t5 hash_ALL PRIMARY #hash#PRIMARY 4 test.t2.a2 1 Using where; Using join buffer (incremental, BNLH join)
# Enable all join buffering capabilities:
SET SESSION join_cache_level = 8;
EXPLAIN
SELECT t4.a4, t5.b5
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.a2 1 Using index
1 SIMPLE t4 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (flat, BNL join)
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t2.a2 1 Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan
SELECT t4.a4, t5.b5
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
a4 b5
9 0
9 NULL
EXPLAIN
SELECT /*+ NO_BNL(t4)*/ t4.a4, t5.b5
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.a2 1 Using index
1 SIMPLE t4 ALL NULL NULL NULL NULL 1 Using where
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t2.a2 1 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
SELECT /*+ NO_BNL(t4)*/ t4.a4, t5.b5
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
a4 b5
9 0
9 NULL
# Disable BKA so the BNLH join buffer will be employed:
EXPLAIN
SELECT /*+ NO_BKA(t5)*/ t4.a4, t5.b5
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.a2 1 Using index
1 SIMPLE t4 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (flat, BNL join)
1 SIMPLE t5 hash_ALL PRIMARY #hash#PRIMARY 4 test.t2.a2 1 Using where; Using join buffer (incremental, BNLH join)
SELECT /*+ NO_BKA(t5)*/ t4.a4, t5.b5
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
a4 b5
9 0
9 NULL
EXPLAIN
SELECT /*+ NO_BKA(t5) NO_BNL(t5)*/ t4.a4, t5.b5
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.a2 1 Using index
1 SIMPLE t4 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (flat, BNL join)
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t2.a2 1 Using where
SELECT /*+ NO_BKA(t5) NO_BNL(t5)*/ t4.a4, t5.b5
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
a4 b5
9 0
9 NULL
DROP TABLE t1,t2,t3,t4,t5;
set @@join_cache_level= @tmp_jcl;
set @@optimizer_switch= @tmp_opt;
Reference in a new issue View git blame Copy permalink