MySQL Slow Subquery using IN Syntax

I found a solution queries that contain a subquery taking forever to return a result in MySQL.

For example a query in MySQL that uses the IN() syntax with a subquery like the following takes a few long time to return a result.

select o.ack,o.product
from orders o
where o.ack in (
select distinct ack
from orders
where status in ('Shipped','Cancelled')
group by ack
having DATE_ADD( DATE_SUB( now( ) , INTERVAL DAYOFMONTH( now( ) ) - 1 DAY ) , INTERVAL -3 MONTH ) max(status_date)))
order by o.ack, o.line

If the same query is reworked to put the subquery in the from with a join it comes back almost instantly.

select o.ack,o.product
from orders o
inner join (select distinct ack
from    orders
where    status in ('Shipped','Cancelled')
group by ack
having    DATE_ADD( DATE_SUB( now( ) , INTERVAL DAYOFMONTH( now( ) ) - 1 DAY ) , INTERVAL -3 MONTH ) > max(status_date)) old_o
on o.ack = old_o.ack
order by o.ack, o.line

It’s interesting on Microsoft MSSQL that the IN() with a subquery is just as fast for me.

bugs.mysql.com/bug.php ( Thanks Jeremy Pointer )