Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.7.2
-
None
Description
Using 11.7.2-MariaDB-ubu2404-log (docker image mariadb:11.7),
I have a left join with Group-By, no matching index on the second table and limit 1:
explain select * from table1 left join table2 on table1.id = table2.table1Id group by table1.id limit 1;
|
+------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------------------------------------------+
|
| 1 | SIMPLE | table1 | ALL | NULL | NULL | NULL | NULL | 13121 | Using temporary; Using filesort |
|
| 1 | SIMPLE | table2 | ALL | NULL | NULL | NULL | NULL | 31740 | Using where; Using join buffer (flat, BNL join) |
|
+------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------------------------------------------+
|
|
|
set join_buffer_size = 16*1024*1024;
|
explain select * from table1 left join table2 on table1.id = table2.table1Id group by table1.id limit 1;
|
+------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------------------------------------------+
|
| 1 | SIMPLE | table1 | ALL | NULL | NULL | NULL | NULL | 13121 | Using temporary; Using filesort |
|
| 1 | SIMPLE | table2 | ALL | NULL | NULL | NULL | NULL | 31740 | Using where; Using join buffer (flat, BNL join) |
|
+------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------------------------------------------+
|
Execution takes 1 min 51.763 sec. (v10.11.11 gave me 1 min 53.750 sec)
set join_cache_level = 3;
|
explain select * from table1 left join table2 on table1.id = table2.table1Id group by table1.id limit 1;
|
+------+-------------+-----------------+----------+---------------+-----------+---------+-----------------------+-------+--------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-----------------+----------+---------------+-----------+---------+-----------------------+-------+--------------------------------------------------+
|
| 1 | SIMPLE | table1 | ALL | NULL | NULL | NULL | NULL | 13121 | Using temporary; Using filesort |
|
| 1 | SIMPLE | table2 | hash_ALL | NULL | #hash#$hj | 4 | db.table1.id | 31740 | Using where; Using join buffer (flat, BNLH join) |
|
+------+-------------+-----------------+----------+---------------+-----------+---------+-----------------------+-------+--------------------------------------------------+
|
Execution takes 0.101 sec.
set join_cache_level = 0;
|
explain select * from table1 left join table2 on table1.id = table2.table1Id group by table1.id limit 1;
|
+------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+
|
| 1 | SIMPLE | table1 | index | NULL | PRIMARY | 4 | NULL | 1 | |
|
| 1 | SIMPLE | table2 | ALL | NULL | NULL | NULL | NULL | 31740 | Using where |
|
+------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+
|
Execution takes 0.007 sec.
Using 10.1.48-MariaDB-1~bionic (docker image library/mariadb:10.1.48) I get:
explain select * from table1 left join table2 on table1.id = table2.table1Id group by table1.id limit 1;
|
+------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------------------------------------------+
|
| 1 | SIMPLE | table1 | ALL | NULL | NULL | NULL | NULL | 13549 | Using temporary; Using filesort |
|
| 1 | SIMPLE | table2 | ALL | NULL | NULL | NULL | NULL | 31210 | Using where; Using join buffer (flat, BNL join) |
|
+------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------------------------------------------+
|
|
|
set join_buffer_size = 16*1024*1024;
|
explain select * from table1 left join table2 on table1.id = table2.table1Id group by table1.id limit 1;
|
+------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+
|
| 1 | SIMPLE | table1 | index | NULL | PRIMARY | 4 | NULL | 1 | |
|
| 1 | SIMPLE | table2 | ALL | NULL | NULL | NULL | NULL | 31210 | Using where |
|
+------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+
|
Execution takes 0.03 sec.
My questions are:
- why join_cache_level defaults to 2 and not 0, 3 or 4?
- why is increasing join_buffer_size causing to avoid BNL in 10.1.48, but not in 11.7 ?
Thanks for your help!