Why Hash Join optimization applies even for Cartesian product?
Jacky Wang <[email protected]> Thu, 13 Oct 2022 18:08:14 -0700 (PDT)
| Newsgroups | comp.databases.mysql |
|---|---|
| Message-ID | <[email protected]> |
In MySQL 8.0 Reference Manual, Hash Join Optimization(https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html), it says that a hash join is also applied for a Cartesian product and Inner non-equi-join can also be optimized using hash joins.
I am curious that how does Hash Join make such queries faster? No any join condition or no equi-join in these cases.
mysql> EXPLAIN FORMAT=TREE
-> SELECT *
-> FROM t1
-> JOIN t2
-> WHERE t1.c2 > 50\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (cost=0.70 rows=1)
-> Table scan on t2 (cost=0.35 rows=1)
-> Hash
-> Filter: (t1.c2 > 50) (cost=0.35 rows=1)
-> Table scan on t1 (cost=0.35 rows=1)
mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 JOIN t2 ON t1.c1 < t2.c1\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t1.c1 < t2.c1) (cost=4.70 rows=12)
-> Inner hash join (no condition) (cost=4.70 rows=12)
-> Table scan on t2 (cost=0.08 rows=6)
-> Hash
-> Table scan on t1 (cost=0.85 rows=6)
Or it is just because MySQL no longer to support BNL?
> Beginning with MySQL 8.0.20, support for block nested loop is removed, and the server employs a hash join wherever a block nested loop would have been used previously.