Optimizing Large-Scale MySQL Join Queries Using EXPLAIN

The article demonstrates how to use EXPLAIN to understand MySQL join execution, identify performance bottlenecks such as Using temporary and Using filesort , and apply targeted optimizations so that first‑page results on million‑row tables return within two seconds.

It explains that MySQL employs a Nested Loop Join algorithm, where the first table in the EXPLAIN output is the driver table. Reducing the number of loop iterations by ensuring the driver table is the smallest result set is crucial for performance.

For two‑table joins, if the driver table is already optimal, no changes are needed. Otherwise, using STRAIGHT_JOIN forces a specific driver table, allowing the optimizer to use the appropriate index and avoid temporary tables.

For multi‑table joins, the same principles apply: prefer left joins, create indexes on all join columns, and keep filtering conditions on the driver table only. When left joins cannot be used throughout, a combination of STRAIGHT_JOIN and careful ordering can still achieve efficient sorting.

Example queries illustrate the difference between using STRAIGHT_JOIN and regular joins; both produce identical results while the former can improve execution plans.

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;

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;

The article also warns against common misconceptions, such as assuming views automatically improve join performance.

References to additional resources on slow query analysis and related topics are provided at the end.