MySQL JOIN Performance and Common Mistakes

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

JOINs work the first time and go wrong later. The query is correct SQL, the server raises nothing, and the answer is a row count you were not expecting or a report that takes 40 seconds. This guide covers those cases: what makes a join slow, and the 4 ways one quietly returns the wrong rows. If you are still deciding which kind of join to write, the tutorial teaches each one against a sample database: how joins work, INNER JOIN, LEFT JOIN, RIGHT JOIN, CROSS JOIN and self join.

The tables used in this guide

CREATE TABLE customers (
    id    INT PRIMARY KEY AUTO_INCREMENT,
    name  VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,    -- nullable: orders without a customer
    amount      DECIMAL(10,2) NOT NULL
);

INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol');
INSERT INTO orders VALUES
    (1, 1, 99.00),   -- Alice's order
    (2, 1, 45.00),   -- Alice's second order
    (3, 2, 120.00),  -- Bob's order
    (4, NULL, 15.00); -- order with no customer

Index the join column

Without an index on the joining column, the server builds a hash table over one side and scans the other. EXPLAIN FORMAT=TREE names it:

EXPLAIN FORMAT=TREE SELECT c.name, o.amount FROM customers c JOIN orders o ON o.customer_id = c.id\G

*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (o.customer_id = c.id)  (cost=2 rows=3)
    -> Table scan on o  (cost=0.117 rows=4)
    -> Hash
        -> Table scan on c  (cost=0.55 rows=3)

A hash join reads each table once. On 7 rows that is nothing. On real tables it is 2 full table scans plus the memory to hold the hash, and when the build side does not fit in join_buffer_size the server spills it to disk in batches. Adding the index replaces all of that with a lookup:

CREATE INDEX idx_orders_customer ON orders (customer_id);

Ask for the same plan again and the hash is gone:

EXPLAIN FORMAT=TREE SELECT c.name, o.amount FROM customers c JOIN orders o ON o.customer_id = c.id\G

*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join  (cost=1.95 rows=4)
    -> Table scan on c  (cost=0.55 rows=3)
    -> Index lookup on o using idx_orders_customer (customer_id=c.id)  (cost=0.378 rows=1.33)

Index the foreign key column on the “many” side of a one-to-many relationship. The primary key on the “one” side is already indexed, so that half needs nothing. In the default EXPLAIN output the same 2 plans read as Extra: Using join buffer (hash join) and type: ref. That table also shows the order the server chose to join in, which matters as soon as a query has 3 or more tables: the server reads them top to bottom, and the row estimate on the first one multiplies through everything below it. Reading EXPLAIN output covers the rest of the plan. An equality condition is what a hash join can look up. Join on a range or an inequality instead and the server still builds the hash, but it has nothing to look up with: the plan reads Inner hash join (no condition) with the test in a Filter above it, so every pair of rows is formed and then thrown away. That is the cost an indexed equality join avoids.

A WHERE clause turns a LEFT JOIN back into an INNER JOIN

The most common wrong-row-count bug, and nothing warns you.

-- Excludes customers with no orders, so this is an inner join now
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.amount > 50;

-- Keeps customers with no orders
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.amount > 50;

The join runs first and gives unmatched customers a NULL o.amount. WHERE then tests each row, NULL > 50 is NULL rather than true, and those rows are dropped. Moving the condition into ON makes it part of what counts as a match instead. The rule: on an outer join, a condition on the inner table belongs in ON, and a condition on the preserved table belongs in WHERE. The exception is WHERE <inner table key> IS NULL, where dropping the matched rows is the point.

A NULL in the join column matches nothing

orders.customer_id is nullable, and order 4 has no customer. An inner join drops it, because NULL is not equal to anything, including another NULL.

SELECT c.name, o.amount
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
ORDER BY c.name, o.amount;

+-------+--------+
| name  | amount |
+-------+--------+
| Alice |  45.00 |
| Alice |  99.00 |
| Bob   | 120.00 |
+-------+--------+
3 rows in set (0.00 sec)

3 rows from 4 orders. Carol is missing because she has no orders, which is the expected half. Order 4 is missing because its customer_id is NULL, which is the half that surprises people: the row exists, its amount is real, and no join condition can reach it. A revenue total built on that join is short by 15.00 and nothing says so. When a join column is nullable, decide what an orphan row should do before you write the join. LEFT JOIN from orders keeps it with a NULL customer name. NULL in MySQL covers the comparison rule behind it.

A one-to-many join multiplies your rows

A join returns 1 row per match, so joining to a table that holds several matching rows repeats the left row once per match. Totals computed over that result are then wrong, usually too high, and the query still looks right.

-- Alice appears twice, because she has 2 orders
SELECT c.name, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.id;

Before treating duplicate rows as a bug, confirm the relationship really is one-to-one. When it is one-to-many and you want 1 row per customer, aggregate: GROUP BY c.id with SUM or COUNT over the joined column. See MySQL GROUP BY performance. The same arithmetic goes wrong faster with 2 one-to-many joins in one query, where the 2 sets of matches multiply against each other. Aggregate each in its own subquery rather than joining both at once.

A missing ON gives you every combination

MySQL accepts INNER JOIN with no ON clause and reads it as a cross join, so a typo produces the product of both tables rather than an error. 2 tables of 10,000 rows give 100 million rows. When a join returns a suspiciously round and suspiciously large number, check for a missing ON before anything else. A comma between tables in FROM is the same thing in older syntax, with the matching condition usually further down in WHERE.

MySQL has no FULL OUTER JOIN

There is no FULL OUTER JOIN in MySQL. Combine the 2 halves with UNION:

SELECT c.name, o.amount FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
UNION
SELECT c.name, o.amount FROM customers c RIGHT JOIN orders o ON o.customer_id = c.id;

UNION removes duplicate rows, which is what makes the 2 halves join up cleanly. UNION ALL does not emulate a full outer join at all: it returns every matched row twice, once from each half. When your data has duplicate rows you need to keep, deduplicate on a key column instead of reaching for UNION ALL.

JOIN or subquery

A join and an IN subquery often express the same filter, and which one the optimizer prefers depends on the data. When you only need to know whether a related row exists and want none of its columns, EXISTS usually reads better and lets the server stop at the first match. Subqueries vs JOINs in MySQL compares them with plans.

Troubleshooting

Problem	Cause and fix
`LEFT JOIN` returns fewer rows than expected	A `WHERE` condition on the right table is dropping the NULL rows. Move it to `ON`.
A join on large tables is slow	The join column on the “many” side has no index. Check `EXPLAIN` for `type: ALL`.
Unexpected duplicate rows	The relationship is one-to-many. Aggregate with `GROUP BY`, or confirm it should be one-to-one.
A total is too high	Same cause: the join repeated the left row, so the sum counts it several times.
A join returns a huge round number of rows	The `ON` clause is missing, so the join is a cross join.
Rows are missing that exist in both tables	The join column is NULL on one side, so nothing matches it.
`RIGHT JOIN` is hard to follow	Rewrite as `LEFT JOIN` with the tables swapped. Same result.

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

The tables used in this guide

Index the join column

A WHERE clause turns a LEFT JOIN back into an INNER JOIN

A NULL in the join column matches nothing

A one-to-many join multiplies your rows

A missing ON gives you every combination

MySQL has no FULL OUTER JOIN

JOIN or subquery

Troubleshooting

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​The tables used in this guide

​Index the join column

​A WHERE clause turns a LEFT JOIN back into an INNER JOIN

​A NULL in the join column matches nothing

​A one-to-many join multiplies your rows

​A missing ON gives you every combination

​MySQL has no FULL OUTER JOIN

​JOIN or subquery

​Troubleshooting

​See also

The tables used in this guide

Index the join column

A WHERE clause turns a LEFT JOIN back into an INNER JOIN

A NULL in the join column matches nothing

A one-to-many join multiplies your rows

A missing ON gives you every combination

MySQL has no FULL OUTER JOIN

JOIN or subquery

Troubleshooting

See also