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

In many projects, especially those with legacy code or after migrating from Oracle, MySQL queries may involve a large number of joined tables, which can lead to production incidents, higher maintenance costs, and performance degradation as data volume grows. The Alibaba development manual even forbids joining three tables in a single query.

To illustrate the issue, the article creates five tables (test1‑test4 and a copy) with primary keys, several indexed columns, and timestamp fields:

CREATE TABLE `test1` (
  `id` TINYINT(3) NOT NULL COMMENT '主键ID',
  `a` VARCHAR(20) DEFAULT NULL,
  `b` VARCHAR(20) DEFAULT NULL,
  `c` VARCHAR(200) DEFAULT NULL,
  `d` TINYINT(3) DEFAULT NULL,
  `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`),
  KEY `c` (`c`),
  KEY `d` (`d`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE test2 LIKE test1;
CREATE TABLE test3 LIKE test1;
CREATE TABLE test4 LIKE test1;

A representative multi‑table join query might look like:

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;

1. Split SQL

Break the large join into smaller queries and combine the results in application 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;

2. Use Temporary Tables

If a joined table (e.g., test3) is large but only a small subset is needed, materialize that subset in a temporary table:

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;

3. Add Redundant Fields

Denormalize by copying the needed column (e.g., test4.d) into test1, eliminating one join:

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;

After adding column t4c to test1, update it from test4 whenever test4.d changes.

4. Leverage Indexes

Both the driver table (chosen by MySQL based on row‑count filtering) and the joined columns should have indexes.

For joins involving multiple columns, create a composite index, e.g.:

SELECT t1.id, t1.a, t2.b, t3.c
FROM test1 t1
JOIN test2 t2 ON t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c;

Avoid index loss: ensure data types match, avoid functions on indexed columns.

Consider STRAIGHT_JOIN to force join order when the optimal driver table is known.

Add indexes on columns used in ORDER BY or LIMIT.

Inspect the execution plan to verify index usage.

SELECT t1.id, t1.a, t2.b, t3.c
FROM test1 t1
STRAIGHT_JOIN test2 t2 ON t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c;

5. Rewrite Query Logic

If a joined table is only used to test existence, replace the join with EXISTS or IN:

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);

6. Reduce Result Set

Add restrictive WHERE clauses to shrink the driver table's result set.

Implement pagination to limit rows returned to the application.

Omit unnecessary columns to reduce join_buffer usage.

7. Adjust Database Configuration

Increasing join_buffer_size or tmp_table_size can help, but such changes affect the whole server and are risky for legacy systems, so they are generally not recommended.

8. Introduce Big‑Data Tools

When joined tables are massive, consider extracting data to a data‑lake or warehouse (e.g., ClickHouse) via ETL pipelines, then query the pre‑processed results. This improves performance at the cost of data freshness.

9. Materialized Summary Table

For queries that do not require real‑time data, schedule a job to populate a summary table and query it directly, optionally caching the result.

CREATE TABLE `test_join_result` (
  `id` TINYINT(3) NOT NULL COMMENT '主键ID',
  `a` VARCHAR(20) DEFAULT NULL,
  `b` VARCHAR(20) DEFAULT NULL,
  `c` VARCHAR(200) DEFAULT NULL,
  `d` TINYINT(3) DEFAULT NULL,
  `e` TINYINT(1) DEFAULT NULL,
  `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

-- Scheduled job
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;

For new systems, avoid multi‑table joins by following coding standards. For existing or migrated systems, evaluate the above techniques and apply the ones that best fit the workload and operational constraints.