Why MySQL Subqueries Slow Down and How to Optimize Them

Introduction

Developers often encounter slow SQL caused by subqueries; converting the subquery to a join and adding proper indexes can resolve the issue. This article introduces the thinking process for tackling slow subqueries.

Principle

MySQL rewrites all queries, including subqueries, as joins and then executes them using nested‑loop algorithms. Contrary to the intuitive belief that a subquery runs once, MySQL actually executes the subquery for every qualifying row of the outer query, which can dramatically degrade performance when the subquery processes many rows or lacks suitable indexes.

When examining an execution plan with EXPLAIN, pay special attention to the select_type column. If it contains SUBQUERY or DEPENDENT SUBQUERY, the query is likely to be slow.

SUBQUERY : the first SELECT inside the subquery. DEPENDENT SUBQUERY : the first SELECT whose result depends on the outer query; if the outer result set is large, the subquery may be executed tens or hundreds of thousands of times.

Optimization Strategies

Semi‑join (pull‑up) : transform the subquery into a join or semi‑join with the parent query.

Materialization : cache the subquery result in memory or a temporary table.

EXISTS strategy : convert the semi‑join into an EXISTS operation, pushing parent conditions into the subquery.

A diagram (below) illustrates MySQL’s subquery optimization pathways.

Optimization Example

The original query took over 1200 ms and was killed by sql‑killer, affecting business operations.

select app_name,pkg_version,zone,created_at 
from activity 
where id in (
    select MAX(id) AS id 
    from activity 
    where zone = 'qa' AND status = 2 AND zanpkg_version != '' 
    AND namespace = 'qa' 
    group by app_name,zone)
order by id desc limit 500;

Execution plan shows that MySQL first retrieves 500 rows, then executes the subquery for each of those rows, scanning around 700 k rows without an appropriate index.

Optimization Methods

Remove the meaningless GROUP BY zone clause because zone='qa' is a constant.

Add a composite index on (zone, namespace, status).

Rewrite the subquery as a join.

select a.app_name, a.zanpkg_version, a.zone, a.created_at 
from activity a, (
    select MAX(id) AS mid 
    from activity 
    where zone = 'qa' AND status = 2 AND zanpkg_version != '' 
    AND namespace = 'qa'
) b 
where a.id = b.mid 
limit 500;

After these changes the query runs in 300‑500 ms, a significant improvement despite still processing large data volumes.

Reference articles: https://www.cnblogs.com/zhengyun_ustc/p/slowquery3.html, https://blog.csdn.net/kk185800961/article/details/49340589, https://blog.csdn.net/fly2nn/article/details/61924636, https://blog.csdn.net/fly2nn/article/details/61924637, https://blog.csdn.net/fly2nn/article/details/61924640.