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!
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| 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:
{noformat} 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) | +------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------------------------------------------+ {noformat} Execution takes 1 min 51.763 sec. {noformat} 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) | +------+-------------+-----------------+----------+---------------+-----------+---------+-----------------------+-------+--------------------------------------------------+ {noformat} Execution takes 0.101 sec. Using 10.1.48-MariaDB-1~bionic (docker image library/mariadb:10.1.48) I get: {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} My questions are: - why join_cache_level defaults to 2 and not 3 or 4? - why has join_buffer_size considered to avoid BNL in 10.1.48, but not in 11.7 ? |
| 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:
{noformat} 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) | +------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------------------------------------------+ {noformat} Execution takes 1 min 51.763 sec. {noformat} 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) | +------+-------------+-----------------+----------+---------------+-----------+---------+-----------------------+-------+--------------------------------------------------+ {noformat} Execution takes 0.101 sec. Using 10.1.48-MariaDB-1~bionic (docker image library/mariadb:10.1.48) I get: {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} My questions are: - why join_cache_level defaults to 2 and not 3 or 4? - why has join_buffer_size considered to avoid BNL in 10.1.48, but not in 11.7 ? |
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:
{noformat} 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) | +------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------------------------------------------+ {noformat} Execution takes 1 min 51.763 sec. {noformat} 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) | +------+-------------+-----------------+----------+---------------+-----------+---------+-----------------------+-------+--------------------------------------------------+ {noformat} Execution takes 0.101 sec. Using *10.1.48*-MariaDB-1~bionic (docker image library/mariadb:10.1.48) I get: {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} My questions are: - why join_cache_level defaults to 2 and not 3 or 4? - why has join_buffer_size considered to avoid BNL in 10.1.48, but not in 11.7 ? |
| 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:
{noformat} 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) | +------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------------------------------------------+ {noformat} Execution takes 1 min 51.763 sec. {noformat} 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) | +------+-------------+-----------------+----------+---------------+-----------+---------+-----------------------+-------+--------------------------------------------------+ {noformat} Execution takes 0.101 sec. Using *10.1.48*-MariaDB-1~bionic (docker image library/mariadb:10.1.48) I get: {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} My questions are: - why join_cache_level defaults to 2 and not 3 or 4? - why has join_buffer_size considered to avoid BNL in 10.1.48, but not in 11.7 ? |
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:
{noformat} 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) | +------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------------------------------------------+ {noformat} Execution takes 1 min 51.763 sec. {noformat} 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) | +------+-------------+-----------------+----------+---------------+-----------+---------+-----------------------+-------+--------------------------------------------------+ {noformat} Execution takes 0.101 sec. Using *10.1.48*-MariaDB-1~bionic (docker image library/mariadb:10.1.48) I get: {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} My questions are: - why join_cache_level defaults to 2 and not 3 or 4? - why is join_buffer_size considered to avoid BNL in 10.1.48, but not in 11.7 ? |
| 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:
{noformat} 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) | +------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------------------------------------------+ {noformat} Execution takes 1 min 51.763 sec. {noformat} 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) | +------+-------------+-----------------+----------+---------------+-----------+---------+-----------------------+-------+--------------------------------------------------+ {noformat} Execution takes 0.101 sec. Using *10.1.48*-MariaDB-1~bionic (docker image library/mariadb:10.1.48) I get: {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} My questions are: - why join_cache_level defaults to 2 and not 3 or 4? - why is join_buffer_size considered to avoid BNL in 10.1.48, but not in 11.7 ? |
h3. 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:
{noformat} 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) | +------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------------------------------------------+ {noformat} Execution takes 1 min 51.763 sec. {noformat} 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) | +------+-------------+-----------------+----------+---------------+-----------+---------+-----------------------+-------+--------------------------------------------------+ {noformat} Execution takes 0.101 sec. h3. Using *10.1.48*-MariaDB-1~bionic (docker image library/mariadb:10.1.48) I get: {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} h3. My questions are: - why join_cache_level defaults to 2 and not 3 or 4? - why is join_buffer_size considered to avoid BNL in 10.1.48, but not in 11.7 ? |
| Description |
h3. 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:
{noformat} 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) | +------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------------------------------------------+ {noformat} Execution takes 1 min 51.763 sec. {noformat} 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) | +------+-------------+-----------------+----------+---------------+-----------+---------+-----------------------+-------+--------------------------------------------------+ {noformat} Execution takes 0.101 sec. h3. Using *10.1.48*-MariaDB-1~bionic (docker image library/mariadb:10.1.48) I get: {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} h3. My questions are: - why join_cache_level defaults to 2 and not 3 or 4? - why is join_buffer_size considered to avoid BNL in 10.1.48, but not in 11.7 ? |
h3. 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:
{noformat} 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) | +------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------------------------------------------+ {noformat} Execution takes 1 min 51.763 sec. {noformat} 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) | +------+-------------+-----------------+----------+---------------+-----------+---------+-----------------------+-------+--------------------------------------------------+ {noformat} Execution takes 0.101 sec. h3. Using *10.1.48*-MariaDB-1~bionic (docker image library/mariadb:10.1.48) I get: {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} Execution takes 0.03 sec. h3. My questions are: - why join_cache_level defaults to 2 and not 3 or 4? - why is join_buffer_size considered to avoid BNL in 10.1.48, but not in 11.7 ? |
| Description |
h3. 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:
{noformat} 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) | +------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------------------------------------------+ {noformat} Execution takes 1 min 51.763 sec. {noformat} 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) | +------+-------------+-----------------+----------+---------------+-----------+---------+-----------------------+-------+--------------------------------------------------+ {noformat} Execution takes 0.101 sec. h3. Using *10.1.48*-MariaDB-1~bionic (docker image library/mariadb:10.1.48) I get: {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} Execution takes 0.03 sec. h3. My questions are: - why join_cache_level defaults to 2 and not 3 or 4? - why is join_buffer_size considered to avoid BNL in 10.1.48, but not in 11.7 ? |
h3. 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:
{noformat} 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) | +------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------------------------------------------+ {noformat} Execution takes 1 min 51.763 sec. {noformat} 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) | +------+-------------+-----------------+----------+---------------+-----------+---------+-----------------------+-------+--------------------------------------------------+ {noformat} {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} Execution takes 0.101 sec. h3. Using *10.1.48*-MariaDB-1~bionic (docker image library/mariadb:10.1.48) I get: {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} Execution takes 0.03 sec. h3. My questions are: - why join_cache_level defaults to 2 and not 3 or 4? - why is join_buffer_size considered to avoid BNL in 10.1.48, but not in 11.7 ? |
| Description |
h3. 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:
{noformat} 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) | +------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------------------------------------------+ {noformat} Execution takes 1 min 51.763 sec. {noformat} 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) | +------+-------------+-----------------+----------+---------------+-----------+---------+-----------------------+-------+--------------------------------------------------+ {noformat} {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} Execution takes 0.101 sec. h3. Using *10.1.48*-MariaDB-1~bionic (docker image library/mariadb:10.1.48) I get: {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} Execution takes 0.03 sec. h3. My questions are: - why join_cache_level defaults to 2 and not 3 or 4? - why is join_buffer_size considered to avoid BNL in 10.1.48, but not in 11.7 ? |
h3. 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:
{noformat} 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) | +------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------------------------------------------+ {noformat} Execution takes 1 min 51.763 sec. {noformat} 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) | +------+-------------+-----------------+----------+---------------+-----------+---------+-----------------------+-------+--------------------------------------------------+ {noformat} {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} Execution takes 0.101 sec. h3. Using *10.1.48*-MariaDB-1~bionic (docker image library/mariadb:10.1.48) I get: {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} Execution takes 0.03 sec. h3. My questions are: - why join_cache_level defaults to 2 and not 0, 3 or 4? - why is join_buffer_size considered to avoid BNL in 10.1.48, but not in 11.7 ? |
| Summary | Left Join Performance | Left Join Performance v10.1.48 vs. v11.7.2 |
| Description |
h3. 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:
{noformat} 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) | +------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------------------------------------------+ {noformat} Execution takes 1 min 51.763 sec. {noformat} 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) | +------+-------------+-----------------+----------+---------------+-----------+---------+-----------------------+-------+--------------------------------------------------+ {noformat} {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} Execution takes 0.101 sec. h3. Using *10.1.48*-MariaDB-1~bionic (docker image library/mariadb:10.1.48) I get: {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} Execution takes 0.03 sec. h3. My questions are: - why join_cache_level defaults to 2 and not 0, 3 or 4? - why is join_buffer_size considered to avoid BNL in 10.1.48, but not in 11.7 ? |
h3. 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: {noformat} 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) | +------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------------------------------------------+ {noformat} Execution takes 1 min 51.763 sec. {noformat} 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) | +------+-------------+-----------------+----------+---------------+-----------+---------+-----------------------+-------+--------------------------------------------------+ {noformat} {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} Execution takes 0.101 sec. h3. Using *10.1.48*-MariaDB-1~bionic (docker image library/mariadb:10.1.48) I get: {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} Execution takes 0.03 sec. h3. My questions are: - why join_cache_level defaults to 2 and not 0, 3 or 4? - why is join_buffer_size considered to avoid BNL in 10.1.48, but not in 11.7 ? |
| Description |
h3. 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: {noformat} 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) | +------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------------------------------------------+ {noformat} Execution takes 1 min 51.763 sec. {noformat} 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) | +------+-------------+-----------------+----------+---------------+-----------+---------+-----------------------+-------+--------------------------------------------------+ {noformat} {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} Execution takes 0.101 sec. h3. Using *10.1.48*-MariaDB-1~bionic (docker image library/mariadb:10.1.48) I get: {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} Execution takes 0.03 sec. h3. My questions are: - why join_cache_level defaults to 2 and not 0, 3 or 4? - why is join_buffer_size considered to avoid BNL in 10.1.48, but not in 11.7 ? |
h3. 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: {noformat} 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) | +------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------------------------------------------+ {noformat} Execution takes 1 min 51.763 sec. {noformat} 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) | +------+-------------+-----------------+----------+---------------+-----------+---------+-----------------------+-------+--------------------------------------------------+ {noformat} Execution takes 0.101 sec. {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} Execution takes 0.007 sec. h3. Using *10.1.48*-MariaDB-1~bionic (docker image library/mariadb:10.1.48) I get: {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} Execution takes 0.03 sec. h3. My questions are: - why join_cache_level defaults to 2 and not 0, 3 or 4? - why is join_buffer_size considered to avoid BNL in 10.1.48, but not in 11.7 ? |
| Description |
h3. 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: {noformat} 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) | +------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------------------------------------------+ {noformat} Execution takes 1 min 51.763 sec. {noformat} 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) | +------+-------------+-----------------+----------+---------------+-----------+---------+-----------------------+-------+--------------------------------------------------+ {noformat} Execution takes 0.101 sec. {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} Execution takes 0.007 sec. h3. Using *10.1.48*-MariaDB-1~bionic (docker image library/mariadb:10.1.48) I get: {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} Execution takes 0.03 sec. h3. My questions are: - why join_cache_level defaults to 2 and not 0, 3 or 4? - why is join_buffer_size considered to avoid BNL in 10.1.48, but not in 11.7 ? |
h3. 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: {noformat} 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) | +------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------------------------------------------+ {noformat} Execution takes 1 min 51.763 sec. {noformat} 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) | +------+-------------+-----------------+----------+---------------+-----------+---------+-----------------------+-------+--------------------------------------------------+ {noformat} Execution takes 0.101 sec. {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} Execution takes 0.007 sec. h3. Using *10.1.48*-MariaDB-1~bionic (docker image library/mariadb:10.1.48) I get: {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} Execution takes 0.03 sec. h3. 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 ? |
| Description |
h3. 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: {noformat} 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) | +------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------------------------------------------+ {noformat} Execution takes 1 min 51.763 sec. {noformat} 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) | +------+-------------+-----------------+----------+---------------+-----------+---------+-----------------------+-------+--------------------------------------------------+ {noformat} Execution takes 0.101 sec. {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} Execution takes 0.007 sec. h3. Using *10.1.48*-MariaDB-1~bionic (docker image library/mariadb:10.1.48) I get: {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} Execution takes 0.03 sec. h3. 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 ? |
h3. 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: {noformat} 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) | +------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------------------------------------------+ {noformat} Execution takes 1 min 51.763 sec. {noformat} 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) | +------+-------------+-----------------+----------+---------------+-----------+---------+-----------------------+-------+--------------------------------------------------+ {noformat} Execution takes 0.101 sec. {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} Execution takes 0.007 sec. h3. Using *10.1.48*-MariaDB-1~bionic (docker image library/mariadb:10.1.48) I get: {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} Execution takes 0.03 sec. h3. 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! |
| Description |
h3. 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: {noformat} 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) | +------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------------------------------------------+ {noformat} Execution takes 1 min 51.763 sec. {noformat} 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) | +------+-------------+-----------------+----------+---------------+-----------+---------+-----------------------+-------+--------------------------------------------------+ {noformat} Execution takes 0.101 sec. {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} Execution takes 0.007 sec. h3. Using *10.1.48*-MariaDB-1~bionic (docker image library/mariadb:10.1.48) I get: {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} Execution takes 0.03 sec. h3. 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! |
h3. 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: {noformat} 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) | +------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------------------------------------------+ {noformat} Execution takes 1 min 51.763 sec. (v10.11.11 gave me 1 min 53.750 sec) {noformat} 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) | +------+-------------+-----------------+----------+---------------+-----------+---------+-----------------------+-------+--------------------------------------------------+ {noformat} Execution takes 0.101 sec. {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} Execution takes 0.007 sec. h3. Using *10.1.48*-MariaDB-1~bionic (docker image library/mariadb:10.1.48) I get: {noformat} 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 | +------+-------------+-----------------+-------+---------------+---------+---------+------+-------+-------------+ {noformat} Execution takes 0.03 sec. h3. 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! |
| Priority | Trivial [ 5 ] | Major [ 3 ] |
| Assignee | Sergei Petrunia [ psergey ] |
| Component/s | Optimizer [ 10200 ] |
| Affects Version/s | 11.7.2 [ 29914 ] |
| Labels | regression |