List:General Discussion« Previous MessageNext Message »
From:Roy Lyseng Date:March 20 2014 1:18pm
Subject:Re: Precedence in WHERE clauses.
View as plain text  
Hi Christophe,

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 
query.

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.

Thanks,
Roy

Thread
Precedence in WHERE clauses.Christophe18 Mar 2014
  • Re: Precedence in WHERE clauses.Michael Dykman18 Mar 2014
    • Re: Precedence in WHERE clauses.Christophe20 Mar 2014
      • Re: Precedence in WHERE clauses.Roy Lyseng20 Mar 2014
  • Re: Precedence in WHERE clauses.Glyn Astill19 Mar 2014
  • Re: Precedence in WHERE clauses.Morgan Tocker19 Mar 2014
    • Re: Precedence in WHERE clauses.Reindl Harald19 Mar 2014
      • Re: Precedence in WHERE clauses.Morgan Tocker19 Mar 2014
        • Re: Precedence in WHERE clauses.Reindl Harald19 Mar 2014
          • Re: Precedence in WHERE clauses.Morgan Tocker19 Mar 2014
            • Re: Precedence in WHERE clauses.Christophe20 Mar 2014
              • Re: Precedence in WHERE clauses.Christophe23 Mar 2014
                • Re: Precedence in WHERE clauses.shawn l.green25 Mar 2014
    • Re: Precedence in WHERE clauses.Mogens Melander19 Mar 2014
      • Re: Precedence in WHERE clauses.shawn l.green20 Mar 2014