MENU


JOIN Optimization

MySQL executes every multi-table query as a sequence of pairwise joins – there is no separate "join engine" beyond the optimizer choosing an order and, per pair, a join algorithm. This page covers how that order and algorithm are chosen, and how to influence them.

Join Order and the Optimizer

For a query joining N tables, the optimizer estimates the cost of candidate join orders (pruned with a greedy search rather than exhaustively for larger N) and picks the cheapest one it finds, using table sizes, index statistics, and estimated row counts from WHERE filtering. The written order of tables in the FROM/JOIN clauses does not determine execution order – the optimizer is free to reorder inner joins however it estimates is cheapest. Outer joins (LEFT JOIN/RIGHT JOIN) constrain this: the outer table must still be processed before its matched inner table in the plan, though the optimizer can still choose which side of the query becomes which.

EXPLAIN SELECT c.name, o.status FROM orders o JOIN customers c ON c.id = o.customer_id WHERE c.country = 'DE';

Written as orders-joins-customers, but if country = 'DE' is highly selective and customers.country is indexed, the optimizer will typically start from customers and probe into orders – check the table column order in EXPLAIN's output, not the query text, to see the actual order chosen.


Driving Table and Driven Table

In a join step, the driving table (outer/first table) is read according to its own access method, and for each qualifying row, the driven table (inner/second table) is probed for matches. A good plan makes the driving table the one that most reduces the row count early – ideally via an index or a highly selective WHERE – so the driven table is probed as few times as possible. If the driven table has a usable index on the join column, each probe is a cheap indexed lookup (type: eq_ref or ref in EXPLAIN); if not, every probe degrades toward a scan.

Nested-Loop Join

MySQL's default join algorithm is (Block) Nested-Loop Join: for each row (or batch of rows) from the driving table, scan or index-probe the driven table. With a usable index on the driven table's join column, this is efficient – it's the standard case shown in most EXPLAIN output with a non-ALL type. Without one, plain nested-loop degenerates into a full scan of the driven table per outer row; Block Nested-Loop (visible as Using join buffer in Extra) mitigates this by buffering a batch of outer rows in join_buffer_size and scanning the inner table once per batch instead of once per row.

Hash Join (MySQL 8.0.18+)

When no usable index exists on the join condition for the driven table, MySQL 8.0.18+ prefers a hash join over Block Nested-Loop: it builds an in-memory hash table from the smaller input (or the whole input, spilling to disk in chunks if it exceeds join_buffer_size) keyed on the join column, then probes it once per row from the other input. This turns an O(rows_a × rows_b) nested scan into roughly O(rows_a + rows_b) work when no index applies. Hash join only applies to equi-joins (ON a.x = b.y); it is never chosen when an index-based nested-loop is available and estimated cheaper. EXPLAIN ANALYZE shows it explicitly as Inner hash join / Left hash join in the plan tree; classic tabular EXPLAIN still reports Using join buffer (hash join) in Extra.

EXPLAIN ANALYZE SELECT o.id, c.name FROM orders o JOIN customers c ON c.email = o.contact_email;

If customers.email and orders.contact_email have no usable index for this comparison, expect Inner hash join in the plan tree rather than a per-row nested scan.


join_buffer_size

join_buffer_size (default 256KB as of MySQL 8.0) is the memory allocated per join that needs a buffer – Block Nested-Loop or hash join. It is allocated per join per session, not once globally, so raising it session-wide (e.g. to 4M) for a reporting connection running large unindexed joins can help, but raising the global default for every connection risks large memory use under concurrency. A missing index on the join column is usually a better long-term fix than growing this buffer.

SET SESSION join_buffer_size = 4 * 1024 * 1024;

Increases the buffer for the current session only, ahead of running a known unindexed join.


STRAIGHT_JOIN

STRAIGHT_JOIN forces the optimizer to join tables in exactly the written order, overriding its own cost-based choice. It's a targeted escape hatch for the rare case where the optimizer's row estimates are misleading it into a worse order (often because of correlated columns or stale statistics it can't see through) – verify with EXPLAIN that it actually improves the plan before keeping it, since it also removes the optimizer's ability to adapt if the data distribution changes later.

SELECT STRAIGHT_JOIN c.name, o.status FROM customers c JOIN orders o ON o.customer_id = c.id WHERE c.country = 'DE';

Forces customers to be read first and orders probed second, regardless of what the optimizer would otherwise pick.


For reading which algorithm and order a specific query actually used, see EXPLAIN and Execution Plans. For the indexes that make nested-loop joins cheap in the first place, see Index Strategy. 

CREATE TABLE customers (
  id       INT UNSIGNED PRIMARY KEY,
  name     VARCHAR(100) NOT NULL,
  country  CHAR(2) NOT NULL,
  email    VARCHAR(255) NOT NULL,
  KEY idx_country (country)
) ENGINE=InnoDB;

CREATE TABLE orders (
  id             BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  customer_id    INT UNSIGNED NOT NULL,
  status         VARCHAR(20) NOT NULL,
  contact_email  VARCHAR(255) NOT NULL,
  KEY idx_customer_id (customer_id)
) ENGINE=InnoDB;

-- Optimizer picks join order; check the "table" column order, not the SQL text
EXPLAIN SELECT c.name, o.status
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'DE';

-- No usable index on the join columns -> MySQL 8.0.18+ prefers a hash join
EXPLAIN ANALYZE
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.email = o.contact_email;

-- Grow the join buffer for one session running a known unindexed join
SET SESSION join_buffer_size = 4 * 1024 * 1024;

-- Force the written join order, overriding the optimizer's choice
SELECT STRAIGHT_JOIN c.name, o.status
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.country = 'DE';