Master MySQL JOIN Optimization: Understanding Driving and Driven Tables

Definition of Driving and Driven Tables

Driving Table

A driving table is the table that MySQL's optimizer selects to query first. It supplies the initial row set that other tables depend on and is usually smaller, more selective, or can efficiently use an index.

Driven Table

A driven table is queried after the driving table, using the intermediate results of the driving table to find matching rows.

Relationship and Factors

The optimizer decides which table becomes the driving table based on the join strategy. Example query:

SELECT * FROM tableA JOIN tableB ON tableA.id = tableB.id;

Key factors influencing the choice:

Data volume – the optimizer prefers the smaller table.

Index usage – a table that can fully utilize an index is favored.

Filter conditions – strong WHERE filters on indexed columns increase the likelihood of being chosen as the driving table.

Execution Order

Steps

MySQL selects a driving table and reads its rows.

For each row from the driving table, it matches rows in the driven table.

The result set contains rows that satisfy the join condition.

Typical Join Types

Nested Loop Join – the driving table is scanned first, and the driven table is probed row‑by‑row.

Hash Join or Block‑Nested Loop – the driving table builds a hash table or index structure that the driven table uses for matching.

Illustrative Example

Schema and Data

CREATE TABLE tableA (id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE tableB (id INT PRIMARY KEY, value VARCHAR(50));
INSERT INTO tableA VALUES (1,'Alice'),(2,'Bob'),(3,'Charlie');
INSERT INTO tableB VALUES (1,'X'),(3,'Y');

EXPLAIN Output

EXPLAIN SELECT * FROM tableA JOIN tableB ON tableA.id = tableB.id \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tableB
type: ALL
possible_keys: PRIMARY
key: NULL
rows: 2
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: tableA
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: article.tableB.id
rows: 1
filtered: 100.00
Extra: NULL

Analysis

tableB is chosen as the driving table because it has fewer rows.

tableA becomes the driven table, matched via tableB.id.

How to Determine the Driving Table

Data volume : Smaller tables are usually selected as the driving table to reduce the amount of data scanned first.

Index availability : Tables that can make full use of an index are preferred, as the index allows rapid row location.

Filter conditions : A table with strong WHERE clauses on indexed columns is more likely to be chosen as the driving table.

JOIN type : For certain joins (e.g., INNER JOIN), the optimizer may reorder tables to pick the most efficient driving table regardless of the logical order in the query.

Optimizing Driving Table Selection

Use EXPLAIN

Run EXPLAIN to see which table the optimizer selects as driving and driven:

EXPLAIN SELECT * FROM tableA INNER JOIN tableB ON tableA.id = tableB.id;

Force a Specific Driving Table

Use a subquery or optimizer hint to influence the choice:

SELECT * FROM (SELECT * FROM tableA WHERE id = 1) AS tempA
JOIN tableB ON tempA.id = tableB.id;

Add Appropriate Indexes

ALTER TABLE tableA ADD INDEX idx_id(id);
ALTER TABLE tableB ADD INDEX idx_id(id);

Summary

The driving table is scanned first and supplies rows for the JOIN condition.

The driven table depends on the intermediate results of the driving table.

Choosing the right driving table improves query performance; consider data size, index availability, filter conditions, and join type. EXPLAIN is a valuable tool to verify and fine‑tune the optimizer's choice.