List:General Discussion« Previous MessageNext Message »
From:Bruce Ferrell Date:January 15 2011 3:25am
Subject:Re: Rewrite SQL to stop table scan
View as plain text  

On 01/14/2011 08:19 AM, Steve Meyers wrote:
> On 1/14/11 3:52 AM, Bruce Ferrell wrote:
>> select count(*) as count
>> from alerts where (unix_timestamp(stamp)>  (unix_timestamp(now()) -
>> '300' ) )
>> and devid = '244';
>>
>
> Bruce -
>
> The problem is that the index is useless, because you're running a
> function on the timestamp.  What you want is this:
>
> SELECT COUNT(*) AS num FROM alerts WHERE stamp > DATE_SUB(NOW(),
> interval 300 second) AND devid=244;
>
> With this query, MySQL will run DATE_SUB() once, and then use the
> index on stamp (which I assume you have) to narrow down the result set.
>
> Steve
>
That did it!  Thank you.
Thread
Rewrite SQL to stop table scanBruce Ferrell14 Jan
  • Re: Rewrite SQL to stop table scanJohan De Meersman14 Jan
  • Re: Rewrite SQL to stop table scanYogesh Kore14 Jan
  • Re: Rewrite SQL to stop table scanYogesh Kore14 Jan
  • Re: Rewrite SQL to stop table scanPeter Brawley14 Jan
  • Re: Rewrite SQL to stop table scanSteve Meyers14 Jan
    • Re: Rewrite SQL to stop table scanBruce Ferrell15 Jan
      • RE: Rewrite SQL to stop table scanJerry Schwartz17 Jan
        • Re: Rewrite SQL to stop table scanSteve Meyers18 Jan
  • RE: Rewrite SQL to stop table scanJerry Schwartz14 Jan