Nested-Loop and Hash Joins

A nested-loop join probes the inner table once per outer row, ideally through an index. A hash join loads the smaller input into a hash table and streams the larger one past it, reading each once. It arrived in 8.0.18, replaced block nested loop in 8.0.20, serves outer, semi- and antijoins too, spills to disk beyond join_buffer_size (256 KB), and obeys BNL/NO_BNL. The classic optimizer uses it only when no index serves the join, so this report over 1.25 million order lines needs hints to get one:

Forcing a hash join for a revenue reportSQL

xa() { sudo mysql shop -Nre "EXPLAIN ANALYZE $1" |
       sed -E 's/^( *)(.*[^ ])  +(\(.*)$/\1\2\n\1     \3/'; }
q="p.category_id, SUM(oi.quantity * oi.unit_price) AS revenue
   FROM order_items AS oi JOIN products AS p ON p.id = oi.product_id GROUP BY p.category_id"
xa "SELECT /*+ NO_JOIN_INDEX(p PRIMARY) NO_JOIN_INDEX(oi product_id) */ $q"

Output

-> Table scan on <temporary>
     (actual time=1251..1251 rows=4 loops=1)
    -> Aggregate using temporary table
         (actual time=1251..1251 rows=4 loops=1)
        -> Inner hash join (oi.product_id = p.id)
             (cost=1.25e+9 rows=1.36e+6) (actual time=10.9..767 rows=1.25e+6 loops=1)
            -> Table scan on oi
                 (cost=0.225 rows=1.25e+6) (actual time=4.01..394 rows=1.25e+6 loops=1)
            -> Hash
                -> Covering index scan on p using category_id
                     (cost=1097 rows=10015) (actual time=1.09..5.7 rows=10000 loops=1)

Unhinted, the nested-loop plan took 2.4 to 9.3 s across runs. The hash join read each table once, in 1.25 s, yet was costed at 1.25e+9. The hypergraph optimizer, off by default and new to the 9.7 Community Edition, costs both properly: after SET optimizer_switch='hypergraph_optimizer=on' it chose the hash join itself, in 0.97 s. It prints only tree and JSON plans (TRADITIONAL fails with error 3999) and can slow simple queries, so try it per session first.

MENU

Nested-Loop and Hash Joins