MYSQL Slow Subquery using IN
I finally found the correct information today to figure out why some kinds of subqueries take forever to come back in mysql. If you are using a subquery with a MYSQL IN() like this:
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
It takes a few minutes to return a result. If you take the same query and reorder it to put the subquery in the from with a join it comes back almost instanty :
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 MSSQL that the IN() subquery syntax is much faster.
Here's a link to the bug report:
bugs.mysql.com/bug.php ( Thanks Jeremy Pointer )
Hippiex, thank you a lot for this post!