Re: Rewrite SQL to stop table scan

[Steve Meyers]

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