Nick Sinclair wrote:
> "[.] WHERE date_format(timestamp, '%Y-%m-%d %T')
> <=DATE_SUB(CURDATE(),INTERVAL $BLACKLIST_DECAY HOUR)"
>
> * ...It only resolves to the DAY and not an hourly resolution. I have
> included a script below that I use for debugging, the MySQL
> functionality is taken directly from one of the larger scripts in my
> package. This is apparent, as the only time any entries are removed, is
> once a day, as the number of hours is "rounded off" to a day, and I can
> see this relected in the logs. Also, FYI, the "$BLACKLIST_DECAY"
> variable from the sourced config file is in "hours".
I don't know the type of the field "timestamp", but I suspect that the
following will work better and faster:
WHERE `timestamp` <= NOW() - INTERVAL $BLACKLIST_DECAY HOUR;
CURDATE() gives you a 'timestamp' of the beginning of today (only date
part with time part as 00:00:00)
NOW() gives you a 'timestamp' of this moment (including time part)
The MySQL optimizer will see that the expression after the <= is a
constant and will produce a fast query (instead of calculating the
DATE_FORMAT() for each row in de table) which can use an index.
Regards, Jigal.
