How to Optimize MySQL Joins for Million‑Row Tables in Under 2 Seconds

This article does not involve complex low‑level data structures; it uses EXPLAIN to interpret SQL and, based on possible situations, performs concrete optimizations so that queries on tables with millions or tens of millions of rows can return the first page of results within two seconds (real‑world alert system optimization). The goal is to help readers understand the SQL execution process and apply optimizations on their own path to success.

When using EXPLAIN, the appearance of Using temporary indicates that a temporary table is created, and Using filesort during pagination means the index cannot be used, requiring adjustments as described below.

Too many rows are scanned, or almost the entire table is read.

The key is NULL. possible_keys lists too many candidate indexes.

1. Use EXPLAIN to Analyze and Tune SQL

MySQL performs joins using a Nested Loop Join algorithm: the result set of the driver (driving) table is iterated, and each row is used as a filter to query the next table, merging the results.

The first table shown in the EXPLAIN output is the driver table.

The driver table can be sorted directly; non‑driver tables require sorting the merged result (temporary table).

The driver table is defined as the table with the fewest rows that satisfies the join condition, or, if no join condition is specified, the smaller table.

Optimization aims to minimize the number of Nested Loop iterations, always using a small result set to drive a larger one.

2. Two‑Table JOIN Optimization

a. When there is no ORDER BY, choose LEFT, RIGHT or INNER join based on the EXPLAIN analysis.

b. When an ORDER BY clause exists (e.g., SELECT * FROM a INNER JOIN b … ORDER BY a.col), use EXPLAIN to verify the driver table. If the driver table is not a, the query may produce Using temporary. In that case, force a to be the driver by using STRAIGHT_JOIN or by rewriting the query so that the WHERE clause contains only conditions on a.

3. Multi‑Table JOIN Optimization

a. Without ORDER BY, use the appropriate join type and tune with EXPLAIN.

b. With an ORDER BY a.col condition, make all joins LEFT JOIN, create indexes on every join column, and keep WHERE conditions only on table a. This builds a large intermediate table based on a and then filters it.

Example forcing the driver table with STRAIGHT_JOIN:

SELECT
    c.*, r.HYPERVISOR_HOST_NAME hostname,
    r.HOST_IP
FROM
    trust_monitor c STRAIGHT_JOIN res_node r ON c.res_node_id = r.ID
    STRAIGHT_JOIN am_assets a ON r.ASSET_ID = a.ID
    AND a.STATUS = 58
    STRAIGHT_JOIN se_role s ON a.DEPT_FLAG = s.ROLE_ORG
    AND s.ROLE_ID IN (32,33,36,41)
WHERE
    c.STATUS = 58
    AND c.changed_type = 79
LIMIT 1,10;

The same query written with ordinary INNER JOIN yields identical results:

SELECT
    c.*, r.HYPERVISOR_HOST_NAME hostname,
    r.HOST_IP
FROM
    trust_monitor c
    INNER JOIN res_node r ON c.res_node_id = r.ID
    INNER JOIN am_assets a ON r.ASSET_ID = a.ID
    AND a.STATUS = 58
    INNER JOIN se_role s ON a.DEPT_FLAG = s.ROLE_ORG
    AND s.ROLE_ID IN (32,33,36,41)
WHERE
    c.STATUS = 58
    AND c.changed_type = 79
ORDER BY
    c.changed_time
LIMIT 1,10;

4. Common Pitfalls

Views only hide or efficiently combine multi‑table data; using a view together with a JOIN does not improve performance.

References: http://www.cnblogs.com/zhengyun_ustc/p/slowquery1.html http://huoding.com/2013/06/04/261