List:General Discussion« Previous MessageNext Message »
From:Steve Meyers Date:January 14 2011 4:19pm
Subject:Re: Rewrite SQL to stop table scan
View as plain text  
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
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