|
CREATE TABLE t1 (f1 INT) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES (1),(2);
|
CREATE TABLE t2 (f2 INT) ENGINE=MyISAM;
|
INSERT INTO t2 VALUES (2),(3);
|
CREATE TABLE t3 (f3 INT, f4 INT) ENGINE=MyISAM;
|
INSERT INTO t3 VALUES (3,3),(4,4);
|
MariaDB [test]> set optimizer_switch='optimize_join_buffer_size=on';
|
Query OK, 0 rows affected (0.001 sec)
|
|
MariaDB [test]> ANALYZE FORMAT=JSON SELECT f4 FROM t2, t3 WHERE f3 IN ( 1, 3 );
|
| {
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 0.2752,
|
"table": {
|
"table_name": "t2",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 2,
|
"r_rows": 2,
|
"r_total_time_ms": 0.019,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"block-nl-join": {
|
"table": {
|
"table_name": "t3",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 2,
|
"r_rows": 2,
|
"r_total_time_ms": 0.0199,
|
"filtered": 100,
|
"r_filtered": 50,
|
"attached_condition": "t3.f3 in (1,3)"
|
},
|
"buffer_type": "flat",
|
"buffer_size": "1",
|
"join_type": "BNL",
|
"r_filtered": 100
|
}
|
}
|
} |
|
The buffer_size here shows 1 which is very less, shouldn't we have some min size that
we always allocate always irrespectiving of number of records being estimated.
|