How to Optimize Multi-Table Joins in MySQL: Practical Strategies

Many MySQL join queries involve too many tables, often due to legacy code or migration from Oracle, which can cause production incidents and higher maintenance costs; Alibaba's development manual even forbids joining three tables.

The article demonstrates the issue with five sample tables (test1‑test4) and a multi‑table join query.

1. Split SQL

Break the multi‑table join into separate queries and combine the results in application code.

<code>SELECT t1.id, t1.a, t2.b, t3.c FROM test1 t1 JOIN test2 t2 ON t1.a=t2.a JOIN test3 t3 ON t1.b=t3.b;
SELECT t1.id, t1.a, t4.d FROM test1 t1 JOIN test4 t4 ON t1.c=t4.c;</code>

2. Use Temporary Table

If a joined table is large but only a small subset is needed, materialize that subset in a temporary table.

<code>CREATE TEMPORARY TABLE temp_t3 (id TINYINT PRIMARY KEY, b VARCHAR(20), INDEX(b)) ENGINE=INNODB;
SELECT t1.id, t1.a, t2.b, t3.c FROM test1 t1 JOIN test2 t2 ON t1.a=t2.a JOIN temp_t3 t3 ON t1.b=t3.b;
SELECT t1.id, t1.a, t4.d FROM test1 t1 JOIN test4 t4 ON t1.c=t4.c;</code>

3. Redundant Fields

Denormalize by copying needed columns (e.g., test4.d) into the main table to eliminate a join, while being aware of update cost and data‑consistency concerns.

<code>SELECT t1.id, t1.a, t2.b, t3.c, t1.t4c FROM test1 t1 JOIN test2 t2 ON t1.a=t2.a JOIN test3 t3 ON t1.b=t3.b AND t3.id <= 1000;</code>

4. Leverage Indexes

Both the driver table and the joined columns should have indexes.

For joins involving multiple columns, create composite indexes.

Avoid index loss caused by type mismatches, functions, or expressions.

Use STRAIGHT_JOIN to force a more optimal join order when appropriate.

Index columns used in ORDER BY or LIMIT clauses.

Inspect index usage with EXPLAIN.

5. Rewrite Queries

Replace joins that only test existence with EXISTS or IN clauses.

<code>SELECT t1.id, t1.a, t2.b, t3.c FROM test1 t1 JOIN test2 t2 ON t1.a=t2.a JOIN test3 t3 ON t1.b=t3.b AND t3.id <= 1000 WHERE EXISTS (SELECT id FROM test4 t4 WHERE t4.d = t1.d);</code>

6. Reduce Result Set

Add restrictive WHERE conditions to shrink the driver table result set.

Apply pagination to limit data returned to the application.

Select only the columns that are actually needed.

7. Adjust DB Configuration

Increasing parameters such as join_buffer_size or tmp_table_size can help but may affect the whole system; it is generally not recommended for legacy environments.

8. Use Big‑Data Tools

When joined tables are extremely large, consider extracting data to a data‑lake or warehouse (e.g., ClickHouse) via ETL pipelines, accepting the trade‑off of lower data freshness.

9. Summary Table

Materialize frequent join results into a summary table or cache for fast reads.

<code>CREATE TABLE test_join_result (
  id TINYINT NOT NULL COMMENT 'primary ID',
  a VARCHAR(20),
  b VARCHAR(20),
  c VARCHAR(200),
  d TINYINT,
  e TINYINT,
  create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'creation time',
  update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'update time',
  PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO test_join_result (id,a,b,c,d)
SELECT t1.id, t1.a, t2.b, t3.c, t4.d
FROM test1 t1 JOIN test2 t2 ON t1.a=t2.a JOIN test3 t3 ON t1.b=t3.b AND t3.id <= 1000 JOIN test4 t4 ON t1.c=t4.c;</code>

New projects should avoid excessive multi‑table joins, while legacy systems require a combination of the above techniques to achieve safe and effective optimization.