Why JOINs Can Be Slow
JOIN Speed Comparison
| JOIN Type | Speed | Use When |
|---|---|---|
| INNER JOIN | Fastest | Need matching rows only |
| LEFT JOIN | Slower | Need all from left table |
| CROSS JOIN | Slowest | Rarely needed |
3 rows
JOINs compare rows from multiple tables. More rows = slower.
Rule 1: Index JOIN Columns
JOIN Speed Comparison
| JOIN Type | Speed | Use When |
|---|---|---|
| INNER JOIN | Fastest | Need matching rows only |
| LEFT JOIN | Slower | Need all from left table |
| CROSS JOIN | Slowest | Rarely needed |
3 rows
-- Create index on the column you join
CREATE INDEX idx_customer ON orders(customer_id);
-- Now this JOIN is fast
SELECT * FROM customers
JOIN orders ON customers.id = orders.customer_id;Rule 2: Filter Early
JOIN Speed Comparison
| JOIN Type | Speed | Use When |
|---|---|---|
| INNER JOIN | Fastest | Need matching rows only |
| LEFT JOIN | Slower | Need all from left table |
| CROSS JOIN | Slowest | Rarely needed |
3 rows
-- Filter with WHERE to reduce rows before joining
SELECT * FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.date > '2024-01-01'; -- Less rows to joinRule 3: Choose Right JOIN
JOIN Speed Comparison
| JOIN Type | Speed | Use When |
|---|---|---|
| INNER JOIN | Fastest | Need matching rows only |
| LEFT JOIN | Slower | Need all from left table |
| CROSS JOIN | Slowest | Rarely needed |
3 rows
Summary
JOIN Speed Comparison
| JOIN Type | Speed | Use When |
|---|---|---|
| INNER JOIN | Fastest | Need matching rows only |
| LEFT JOIN | Slower | Need all from left table |
| CROSS JOIN | Slowest | Rarely needed |
3 rows
- Index columns used in ON clause
- Filter early with WHERE
- Use INNER JOIN when possible