2 views collected around this technical thread.
This article explains how multi‑table joins in MySQL can become performance bottlenecks and walks through the four join algorithms—Simple Nested‑Loop, Block Nested‑Loop, Hash, and Index Nested‑Loop—showing their execution plans, code examples, and step‑by‑step optimizations using indexes and join buffers to dramatically improve query speed.
This article explains how MySQL processes multi‑table joins, compares Simple Nested‑Loop, Block Nested‑Loop, Hash, and Index Nested‑Loop join algorithms, and demonstrates practical optimization techniques using indexes, join buffers, and query rewrites to dramatically improve performance on large datasets.
The article explains Presto’s join processing by detailing the business need to limit multi‑table joins, then describing nested‑loop, sort‑merge, and hash join algorithms with Java examples, and finally showing how the Volcano model, columnar pages, and planner integration enable scalable, efficient OLAP join execution.
The article explores MySQL's Hash Join algorithms, comparing Basic, On-disk, Grace, and Hybrid variants, while detailing rule-based optimizer decisions, memory management, disk spilling mechanisms, and outer join execution strategies through source code analysis.
This article explains how MySQL 8.0.18 introduces the hash join algorithm, describes its build and probe phases, disk‑overflow handling, usage instructions, optimizer switches, performance comparisons with block nested loops, and current limitations, providing code examples and practical guidance.
This article explains the purpose and configuration of MySQL's join_buffer_size for INNER JOIN operations without indexed join keys, compares Nested Loop, Block Nested Loop, and the newly introduced Hash Join algorithms, and shows how each affects execution plans and query performance.