Nested-Loop or Hash Join in MySQL?

Up to MySql 8 there is no hash join algorithm in MySQL. Nested-loop is generally preffered way when joining two tables columns have indexes shown as below. In example product_order has 5.000.000 rows and product_customers has 1000 rows. MySQL version 9.2 in example has chosen nested-loop join method, cost is 2.28+e6 and actual time is 14.632 ms.

Press enter or click to view image in full size

Nested-Loop join Query Plan

What if we force MySql to use hash-join instead of nestloop. To force hash join we use no_index hint optimizer hint in query as below image. Even though cost is 2x higher, actual time is reduced from 14.632 ms to 7.484 which is 2x is better when using hash join then nested-loop.

Press enter or click to view image in full size

Hash-Join Plan

If you join two tables and one of is big table without filtered much rows then you can try to use hash join to optimize your query.