On 20.03.14 13:18, Christophe wrote:
> Hi Michael,
> Le 18/03/2014 20:28, Michael Dykman a écrit :
>> Also, as you currently have it, the expression DATE_SUB(NOW(),
>> INTERVAL 24 is going to be executed once for every single candidate
>> row. I would suggest you temporarily memoize that like so:
>> select into DATE_SUB(NOW(), INTERVAL 24 HOUR) INTO @yesterday ;
>> SELECT * FROM Status WHERE DWProcessed = 0 AND PreviousStatus NOT IN
>> ('PENDING', 'ACCEPTED') AND SubscribeDate < @yesterday);
> Indeed ! it can be a significant performance improvement ;).
> Thanks for this .
Actually, the optimizer is often capable of detecting constant expressions and
evaluating them only once, regardless of how many rows that are accessed in the
Run EXPLAIN EXTENDED for the query, followed by SHOW WARNINGS.
If you see <cache>(<your expression>) in the output, it is a sign that the
optimizer detected that <your expression> is constant, so it will be evaluated
once and cached for subsequent uses.