How to Speed Up a 10‑Table JOIN Query in MySQL (JD Interview Scenario)

Introduction

A candidate in a JD second‑round interview was asked how to troubleshoot and optimize a SQL statement that joins ten tables and runs longer than 30 seconds. The challenge lies not in writing the SQL itself but in applying a systematic, multi‑layered diagnostic and optimization process.

1. Locate the Bottleneck

1.1 Use EXPLAIN to Analyze the Execution Plan

Run EXPLAIN and examine the type, rows, and Extra columns. Look for ALL or index scans (which should be replaced by ref, eq_ref, or const), unusually large rows estimates, and Using temporary or Using filesort in Extra, which are major performance killers.

Example : an EXPLAIN output shows users.type=ALL, indicating a missing index on user_id —the most direct optimization point.

1.2 View Real Execution Time Distribution

<code>SET profiling = 1;</code>
-- execute the slow SQL
SELECT ...;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

The result displays the time spent in each phase (e.g., sending data, creating sort index), helping you decide whether the bottleneck is I/O‑bound or CPU‑bound.

1.3 Check Database Parameter Settings

join_buffer_size

: too small causes repeated scans. tmp_table_size / max_heap_table_size: too large leads to on‑disk temporary tables. innodb_buffer_pool_size: must be large enough to hold hot data.

2. Why a 10‑Table JOIN Is Slow

MySQL (InnoDB) uses a Nested Loop Join by default: it reads rows from the driver table and, for each row, scans the next table using its index. The time complexity is roughly driver_rows × (index_scan_cost_per_join). For example, with a driver table of 100 k rows and an index‑scan cost of 1 ms per join, ten joins cost about 1 000 seconds. MySQL 8.0.18 introduced Hash Join, which can be faster if all join predicates are indexed, but it still depends on memory and hash‑building overhead.

3. Seven Optimization Weapons

Weapon 1: Index Optimization (Immediate Impact)

Ensure every column used in ON and WHERE clauses has an appropriate index. For LEFT JOIN, the right‑table join column must be indexed, and composite indexes should follow the left‑most‑prefix rule.

-- Add a composite index on orders
ALTER TABLE orders ADD INDEX idx_create_user (create_time, user_id);
-- Add an index on users
ALTER TABLE users ADD INDEX idx_vip (vip_level);
-- Add an index on categories
ALTER TABLE categories ADD INDEX idx_status (status);

After adding indexes, the access type changes from ALL to ref or range, eliminating full‑table scans.

Weapon 2: Adjust JOIN Order

Make the smaller table the driver to reduce loop iterations. Use STRAIGHT_JOIN to force the desired order.

-- Original (large table drives)
SELECT o.* FROM orders o JOIN blacklist b ON o.user_id = b.user_id;
-- Forced small‑table driver
SELECT STRAIGHT_JOIN o.* FROM blacklist b JOIN orders o ON b.user_id = o.user_id;

Verify with EXPLAIN that the first row is the small table (e.g., rows≈100).

Weapon 3: Split JOIN + Application‑Side Assembly

When the result is a list and the data volume is moderate, query the main table first, batch‑fetch related IDs, and assemble the full objects in Java.

// 1. Query main orders without joins
List<Order> orders = orderMapper.selectList(new LambdaQueryWrapper<Order>()
    .gt(Order::getCreateTime, startTime)
    .last("limit 20"));
if (orders.isEmpty()) return Collections.emptyList();
// 2. Extract ID sets
Set<Long> userIds = orders.stream().map(Order::getUserId).collect(Collectors.toSet());
// ... similarly for productIds, addressIds, payIds
// 3. Batch query related tables
Map<Long, User> userMap = batchQuery(userIds, userMapper::selectBatchIds);
// 4. Assemble in memory
orders.forEach(o -> {
    o.setUser(userMap.get(o.getUserId()));
    // set other relations
});
return orders;

This reduces database pressure at the cost of additional application code.

Weapon 4: Use Temporary or Derived Tables

Materialize intermediate results that are reused multiple times to avoid repeated computation.

-- Create temporary table for user order totals
CREATE TEMPORARY TABLE tmp_user_stat (
    user_id BIGINT PRIMARY KEY,
    total DECIMAL(10,2),
    INDEX(user_id)
) ENGINE=InnoDB;
INSERT INTO tmp_user_stat (user_id, total)
SELECT user_id, SUM(amount) FROM orders GROUP BY user_id;
SELECT u.name, u.level, t.total FROM users u JOIN tmp_user_stat t ON u.id = t.user_id WHERE u.status='ACTIVE';
DROP TEMPORARY TABLE IF EXISTS tmp_user_stat;

Advantages: avoids duplicate GROUP BY; disadvantages: extra storage and cleanup.

Weapon 5: Materialized View / Summary Table

For relatively static BI reports, pre‑compute results into a summary table refreshed nightly.

CREATE TABLE daily_sales_report (
    report_date DATE,
    product_id BIGINT,
    region VARCHAR(50),
    total_amount DECIMAL(12,2),
    order_count INT,
    PRIMARY KEY(report_date, product_id, region)
);
INSERT INTO daily_sales_report
SELECT DATE(o.create_time), p.id, a.region, SUM(o.amount), COUNT(*)
FROM orders o
JOIN products p ON o.product_id = p.id
JOIN users u ON o.user_id = u.id
JOIN address a ON u.address_id = a.id
WHERE o.create_time >= CURDATE() - INTERVAL 1 DAY
  AND o.create_time < CURDATE()
GROUP BY DATE(o.create_time), p.id, a.region;

Querying the summary table yields millisecond‑level responses; the trade‑off is data latency and storage cost.

Weapon 6: Switch to an OLAP Engine

For analytical workloads, migrate data to column‑oriented engines such as ClickHouse or Doris, which handle wide tables and star‑schema joins efficiently.

-- ClickHouse example with GLOBAL JOIN
SELECT o.order_no, u.name, p.product_name
FROM orders_local o
GLOBAL JOIN users_local u ON o.user_id = u.id
GLOBAL JOIN products_local p ON o.product_id = p.id
SETTINGS join_algorithm='partial_merge';

Pros: extreme performance on petabyte‑scale data; cons: added operational complexity and lack of transactional support.

Weapon 7: Vertical Partitioning + Read/Write Splitting

Separate large, low‑frequency columns into an extension table and route heavy analytical queries to replicas.

-- Base table with frequently accessed columns
CREATE TABLE orders_basic (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    amount DECIMAL(10,2),
    status VARCHAR(20),
    create_time DATETIME
);
-- Extension table with large text fields
CREATE TABLE orders_ext (
    order_id BIGINT PRIMARY KEY,
    remark TEXT,
    delivery_address TEXT
);
-- Query base fields only when possible, LEFT JOIN ext when needed

Benefits: smaller row size improves cache hits; drawbacks: increased application complexity.

4. Choosing the Right Weapon

Each technique has distinct advantages, disadvantages, and suitable scenarios. Indexing is quick and low‑impact but can hurt write performance if overused. Adjusting join order costs little but requires knowledge of data distribution. Splitting queries and using temporary tables shift load to the application layer. Materialized views and OLAP engines deliver near‑instant query times for reporting but introduce latency and maintenance overhead. Vertical partitioning and read/write splitting are architectural changes best suited for high‑write, complex‑query workloads.

5. Sample Interview Answer

Start with EXPLAIN to locate full‑table scans or temporary tables, then add or adjust indexes and consider join order. If the SQL layer is still insufficient, propose application‑side batch queries or materialized tables, and for heavy analytical use cases suggest moving the workload to ClickHouse or a similar OLAP engine. Finally, discuss possible denormalization to reduce the number of joins.