This article explains the JOIN order optimization technique known as the ‘small table drives large table’ principle, demonstrates its impact with a real e‑commerce query case, and reviews various MySQL join algorithms, their trade‑offs, and practical tuning tips.
This article analyzes a slow SQL query by examining driver tables, execution plans, and table row counts, then demonstrates three optimization approaches—setting a small table as driver, setting a large table as driver, and switching to hash joins—showing their impact on execution time and providing practical guidelines for selecting the best strategy.
The article explains why multi‑table JOINs in MySQL can degrade performance, readability, and index usage, and it presents optimization strategies such as query decomposition, data redundancy, wide tables, and introduces the hash join algorithm with detailed build and probe phases, including disk‑based handling.
LibraDB, the column‑store replica of TDSQL MySQL, has evolved its execution engine from a simple scatter‑gather model to a vectorized SMP pipeline that integrates MPP parallelism, asynchronous I/O, SIMD‑accelerated aggregation and join operators, work‑stealing, and runtime filters, thereby fully exploiting CPU, memory, network and disk resources for both OLTP and analytical queries.
This article explains MySQL's hash join algorithm, compares it with nested‑loop joins, shows how to read execution steps and EXPLAIN output, discusses extended hash‑join scenarios, and weighs the trade‑offs of performing multi‑table joins inside the database versus in application code.
This article explains why hash joins usually outperform nested‑loop joins, how to force hash joins in SQL, the data‑type restrictions that prevent hash joins, and practical tips for optimizing join performance in TD and Oracle compatibility modes.
This article explains the characteristics of various MySQL join algorithms—including Simple Nested‑Loop, Index Nested‑Loop, Block Nested‑Loop, Hash Join, and Batched Key Access—demonstrates how to examine execution plans, configure optimizer settings, and apply best‑practice optimizations such as indexing, choosing the small driver table, and upgrading MySQL versions.
This article provides a comprehensive overview of MySQL 8.0 enhancements, covering performance improvements such as contention‑aware scheduling and hash joins, security upgrades including authentication plugins and password policies, optimizer refinements, and various other features like persistent variables, GIS support, and binlog expiration handling.
This article examines ClickHouse's join mechanisms, detailing the limitations of standard joins, the advantages of Global joins, and optimization strategies such as hash and merge joins, subquery filtering, and memory considerations, illustrated with SQL examples and source‑code analysis.
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 the concepts of driving (outer) and inner tables in MySQL join algorithms, covering Nested‑Loop, Block Nested‑Loop, and Hash Join implementations, how the optimizer selects the outer table, and comparisons with other database systems.
The article explains MySQL 8.0.18's new hash join capability, shows how to enable and view it with EXPLAIN FORMAT=TREE, provides sample SQL and table definitions, compares its performance against block nested‑loop joins under various conditions, and discusses configuration limits and tuning tips.
The first episode of the “MySQL Chōng Chōng Chōng” livestream discusses why now is an excellent time to upgrade to MySQL 8.0, covering fast add‑column, Atomic DDL, Hash Join, Group Replication, authentication changes, and includes thanks to sponsors and a preview of the next show.
This article explains the concept of Hash Join in MySQL 8.0, compares it with Nested Loop joins, shows how to enable or force it with server variables or hints, and presents performance benchmarks that demonstrate its speed advantages on large datasets.
Learn how MySQL 8’s hash join algorithm works by building an in‑memory hash table from the smaller table, probing the larger table for matches, and handling oversized tables through disk‑based chunk files, offering a more efficient alternative to traditional nested‑loop joins.
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 SparkSQL's three join strategies—Shuffle Hash Join, Broadcast Hash Join, and Sort Merge Join—detailing their mechanisms, when to use each based on table size, and their relative performance costs in distributed big‑data environments.
MySQL 8.0 introduces Hash Join for dramatically faster large‑dataset queries, supports in‑memory and spill‑to‑disk processing, can replace Block Nested‑Loop joins, and adds EXPLAIN ANALYZE with detailed runtime statistics and tree‑style output, highlighting the platform’s rapid evolution.
This article explains why a SQL Server paging query using TOP can take minutes, demonstrates how switching to ROW_NUMBER() with CTEs, index hints, temporary tables, and hash joins dramatically reduces execution time, and provides practical tips for handling large page numbers.
This article explains Oracle's parallel query feature, covering its benefits, resource costs, required conditions, various data‑distribution methods such as broadcast, replicate and hash, how to read parallel execution plans, and practical monitoring techniques to avoid performance pitfalls.
