Big Move to AZ Day 3
Cool StuffWe're in Albuquerque New Mexico! Next Stop AZ.
Add Comment
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
Persistent Storage for Amazon EC2
Cool StuffOn the heals of GOOGLE Apps having persistent storage Amazon decides to add it as well. This was the only major issue I had with EC2 as I didn't "get" how it would all work with S3. It seems they are planning to add an unformatted harddrive that you can partition and format to your heart's desire. There will even be an API to backup the harddrive to S3.
This is a very exciting development, I can't wait to try it out.
http://developer.amazonwebservices.com/connect/thread.jspa?threadID=21082
Add Comment
Once
Music, Movies
I just watched this movie with my girlfriend the other night. I had heard good things about it, but I was shocked how much I actually liked it.
It's a very unconventional love story. Maybe that's what made me enjoy it so much. The two lead characters obviously really connect through thier love of music. I won't spoil the ending for those of you who haven't seen it, but it really ends in a very unusual way as most movies go. Which I think actually makes the movie even better.
The most impressive thing about this movie is the that it made me buy a soundtrack which I haven't done in a long time. After checking it out on Wikipedia I was a little disappointed to find out he was in a " very popular Irish band ". While I was viewing the movie Glen Hansard did such a good job of being the struggling musician I really believed it :). He did write all the music with Markéta Irglová which is very cool. Their voices are great and I am digging it a lot.
If you get a chance you should check it out.
Add Comment
Loading....