Description:
Hi,
There is a new feature in MySQL 8.0.18 called Hash Joins, while I was testing it I noticed a few things which could be improved.
1) Even if hash join is used the traditional explain says "Using join buffer (Block Nested Loop)".
Not every one who is using MySQL reading the release notes and they wont know there is a hash join now and they should use "explain format=tree".
I would recommend that if Hash Join used the traditional explain should say Hash Join as well.
2) If we disable hash join with a hint or optimizer the "explain format=tree" output gives a message which is not very useful: "<not executable by iterator executor>" I would recommend a better message here which actually tells hash join is disabled or something...
How to repeat:
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`c1` int(11) NOT NULL DEFAULT '0',
`c2` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_c1` (`c1`)
) ENGINE=InnoDB;
CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`c1` int(11) NOT NULL DEFAULT '0',
`c2` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_c1` (`c1`)
) ENGINE=InnoDB;
insert into t1 values (1,1,1),(2,2,2),(3,3,3),(4,4,4);
insert into t2 values (1,1,1),(2,2,2),(3,3,3),(4,4,4);
mysql> explain format=tree select count(*) from t1 join t2 on t1.c2 = t2.c2;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Aggregate: count(0)
-> Inner hash join (t2.c2 = t1.c2) (cost=2.50 rows=4)
-> Table scan on t2 (cost=0.09 rows=4)
-> Hash
-> Table scan on t1 (cost=0.65 rows=4)
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> explain select count(*) from t1 join t2 on t1.c2 = t2.c2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
filtered: 25.00
Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.00 sec)
Explain is lying here and says "Nested Loop"
==================
mysql> SET optimizer_switch='hash_join=off';
Query OK, 0 rows affected (0.00 sec)
mysql> explain format=tree select count(*) from t1 join t2 on t1.c2 = t2.c2;
+----------------------------------------+
| EXPLAIN |
+----------------------------------------+
| <not executable by iterator executor>
|
+----------------------------------------+
1 row in set (0.00 sec)
mysql> SET optimizer_switch='hash_join=on';
Query OK, 0 rows affected (0.00 sec)
mysql> explain format=tree select /*+ NO_HASH_JOIN (t1,t2) */ count(*) from t1 join t2 on t1.c2 = t2.c2;
+----------------------------------------+
| EXPLAIN |
+----------------------------------------+
| <not executable by iterator executor>
|
+----------------------------------------+
1 row in set (0.00 sec)
The message is not very helpful.
Suggested fix:
- Explain and "explain format=tree" should give us the same information , at the moment even if the query is using Hash Join normal explain is lying and says "Nested Loop"
- And please improve the message if Hash Join is disabled.
Description: Hi, There is a new feature in MySQL 8.0.18 called Hash Joins, while I was testing it I noticed a few things which could be improved. 1) Even if hash join is used the traditional explain says "Using join buffer (Block Nested Loop)". Not every one who is using MySQL reading the release notes and they wont know there is a hash join now and they should use "explain format=tree". I would recommend that if Hash Join used the traditional explain should say Hash Join as well. 2) If we disable hash join with a hint or optimizer the "explain format=tree" output gives a message which is not very useful: "<not executable by iterator executor>" I would recommend a better message here which actually tells hash join is disabled or something... How to repeat: CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT , `c1` int(11) NOT NULL DEFAULT '0', `c2` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `idx_c1` (`c1`) ) ENGINE=InnoDB; CREATE TABLE `t2` ( `id` int(11) NOT NULL AUTO_INCREMENT , `c1` int(11) NOT NULL DEFAULT '0', `c2` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `idx_c1` (`c1`) ) ENGINE=InnoDB; insert into t1 values (1,1,1),(2,2,2),(3,3,3),(4,4,4); insert into t2 values (1,1,1),(2,2,2),(3,3,3),(4,4,4); mysql> explain format=tree select count(*) from t1 join t2 on t1.c2 = t2.c2; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Aggregate: count(0) -> Inner hash join (t2.c2 = t1.c2) (cost=2.50 rows=4) -> Table scan on t2 (cost=0.09 rows=4) -> Hash -> Table scan on t1 (cost=0.65 rows=4) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> explain select count(*) from t1 join t2 on t1.c2 = t2.c2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 filtered: 100.00 Extra: NULL *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 filtered: 25.00 Extra: Using where; Using join buffer (Block Nested Loop) 2 rows in set, 1 warning (0.00 sec) Explain is lying here and says "Nested Loop" ================== mysql> SET optimizer_switch='hash_join=off'; Query OK, 0 rows affected (0.00 sec) mysql> explain format=tree select count(*) from t1 join t2 on t1.c2 = t2.c2; +----------------------------------------+ | EXPLAIN | +----------------------------------------+ | <not executable by iterator executor> | +----------------------------------------+ 1 row in set (0.00 sec) mysql> SET optimizer_switch='hash_join=on'; Query OK, 0 rows affected (0.00 sec) mysql> explain format=tree select /*+ NO_HASH_JOIN (t1,t2) */ count(*) from t1 join t2 on t1.c2 = t2.c2; +----------------------------------------+ | EXPLAIN | +----------------------------------------+ | <not executable by iterator executor> | +----------------------------------------+ 1 row in set (0.00 sec) The message is not very helpful. Suggested fix: - Explain and "explain format=tree" should give us the same information , at the moment even if the query is using Hash Join normal explain is lying and says "Nested Loop" - And please improve the message if Hash Join is disabled.