Why a Simple MySQL IN Subquery Takes 33 Seconds and How to Fix It

Preface

MySQL is a widely used database in many projects, and the IN clause is a common way to filter rows. During a recent debugging session we encountered a SELECT query that unexpectedly took 33 seconds to run.

1. Table Structure

userinfo table

article table

2. Problem SQL Example

select * from userinfo where id in (select author_id from article where type = 1);

At first glance this looks like a simple subquery: fetch author_id from article and use it in an IN filter on userinfo . With proper indexes this would be fast.

However, the actual execution took 33 seconds.

3. Root Cause

The MySQL optimizer in version 5.5 may rewrite an IN subquery into a row‑by‑row EXISTS scan, causing the subquery to be evaluated for each outer row. This behavior was optimized in MySQL 5.6.

4. Solutions (MySQL 5.5)

1. Use a temporary table

select id, username from userinfo where id in (select author_id from article where type = 1) as tb;

2. Use JOIN

select a.id, a.username from userinfo a, article b where a.id = b.author_id and b.type = 1;

5. Additional Notes

MySQL 5.6 introduced subquery materialization, which avoids the rewrite to EXISTS and executes the subquery only once, similar to the temporary‑table approach.

If materialization is not used, the optimizer sometimes rewrites a non‑correlated subquery as a correlated subquery. For example, the following IN subquery is non‑correlated: select * from t1 where t1.a in (select t2.b from t2 where where_condition); The optimizer might rewrite it as: select * from t1 where exists (select t2.b from t2 where where_condition and t1.a = t2.b); Using a temporary table (materialization) avoids such rewrites and improves performance.