1. Forum
  2. Usenet
  3. COMP.DATABASES.MYSQL

  • Why Hash Join optimization applies even for Cartesian product?

    From Jacky Wang@21:1/5 to All on Thu Oct 13 18:08:14 2022
    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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)