novaBBS - comp.databases.mysql - Why Hash Join optimization applies even for Cartesian product?

Why Hash Join optimization applies even for Cartesian product?

<9088f8f3-25b8-4d01-9195-cf45ccc88ed3n@googlegroups.com>

https://www.novabbs.com/devel/article-flat.php?id=92&group=comp.databases.mysql#92

copy link Newsgroups: comp.databases.mysql

X-Received: by 2002:a05:6214:1c44:b0:4b3:203b:7383 with SMTP id if4-20020a0562141c4400b004b3203b7383mr2135131qvb.69.1665709694882;
Thu, 13 Oct 2022 18:08:14 -0700 (PDT)
X-Received: by 2002:a0d:ca0d:0:b0:357:2905:890f with SMTP id
m13-20020a0dca0d000000b003572905890fmr2508192ywd.54.1665709694700; Thu, 13
Oct 2022 18:08:14 -0700 (PDT)
Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!usenet.blueworldhosting.com!feed1.usenet.blueworldhosting.com!peer01.iad!feed-me.highwinds-media.com!news.highwinds-media.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.databases.mysql
Date: Thu, 13 Oct 2022 18:08:14 -0700 (PDT)
Injection-Info: google-groups.googlegroups.com; posting-host=38.99.100.2; posting-account=R1au5AoAAACUxi-U5j0D0zb9NoGw5Fxy
NNTP-Posting-Host: 38.99.100.2
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <9088f8f3-25b8-4d01-9195-cf45ccc88ed3n@googlegroups.com>
Subject: Why Hash Join optimization applies even for Cartesian product?
From: fangsboy...@gmail.com (Jacky Wang)
Injection-Date: Fri, 14 Oct 2022 01:08:14 +0000
Content-Type: text/plain; charset="UTF-8"
X-Received-Bytes: 2382

by: Jacky Wang - Fri, 14 Oct 2022 01:08 UTC

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.

Remember, UNIX spelled backwards is XINU. -- Mt.

devel / comp.databases.mysql / Why Hash Join optimization applies even for Cartesian product?

Subject	Author
Why Hash Join optimization applies even for Cartesian product?	Jacky Wang