Jigal van Hemert wrote:
> 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.
>
Thanks Jigal, all works great - You are *so* right.
