Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.6
-
None
Description
Join_cache_level=3 allows using only flat/incremental BNL and flat BNLH join buffers, but in fact BKA buffer is employed:
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=off,join_cache_bka=on,mrr=on'; |
set join_cache_level = 3; |
EXPLAIN SELECT * FROM t12, t13 |
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1); |
|
drop table t10, t12, t13; |
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 |
|
+------+-------------+-------+------+---------------+------+---------+------------+------+------------------------------------------------------------------------------------+
|
Attachments
Issue Links
- relates to
-
MDEV-36133 Hint BNL() doesn't work when join_cache_level>= 5 or join_cache_hashed=off
-
- Closed
-