List:General Discussion« Previous MessageNext Message »
From:dpgirago Date:November 19 2007 10:12pm
Subject:Query Optimization
View as plain text  
I need to check a date_time value in a number of tables for 
"up-to-date-ness." 

The following query is part of a larger PHP script that runs as a cron job 
every 10 minutes:

<query>
select 
        case 
                # If within 2 minutes, do nothing.
                when (unix_timestamp() - unix_timestamp(date_time)) < 120 
                then 'ok' 
 
                # If between 2 and 60 minutes old, send an email each time 
the script is called (q 10 min).
                when (unix_timestamp() - unix_timestamp(date_time)) >= 120 
 && 
                (unix_timestamp() - unix_timestamp(date_time)) < 3600 
                then 'email'

                # If over an hour old, send out one email per hour. 
                when (unix_timestamp() - unix_timestamp(date_time)) >= 
3600 && 
                (unix_timestamp() - unix_timestamp(date_time)) % 3600 > 
2999 && 
                (unix_timestamp() - unix_timestamp(date_time)) % 3600 < 
3600 
                then 'email'

        end 
        as 'test'

from  mytable order by date_time desc limit 1;

</qyery>

This seems to run OK, but I'd be interested if anyone sees any way to 
improve it.

Thanks,

--David.
Thread
Query Optimizationdpgirago19 Nov