MYSQL Slow Subquery using IN
Coldfusion, mySQLI 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.lineIt'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 :) )
No comments for this entry.
|| 
Add Comment
Add Comment
Loading....