Re: Rewrite SQL to stop table scan

[Bruce Ferrell]

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.