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 :) )
Add Comment
Mysql Joined Updates
Coldfusion, mySQLI can never seem to remember the mySQL syntax for JOINED updates when I need them and the mysql documentation on their site doesn't help a whole lot. Then I always forget what the exact phase to google for. (Joined updates, multi-table updates, updating from one table to another, etc..) I found some great examples over on the electictoolbox that makes it dead simple.
So I am posting it here as a reminder later on , and in case someone else was wondering how to do it and couldn't find it.
ELECTRICTOOLBOX JOINED UPDATE EXAMPLES
Add Comment
Loading....