List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:January 17 2011 4:52pm
Subject:RE: Rewrite SQL to stop table scan
View as plain text  
<snip>
>>
>> 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.
>>
[JS] I don't understand how an index on a timestamp would help. Theoretically, 
each record could have a unique value for the timestamp; so the index would 
have an entry for each record. Would MySQL really use that in preference to, 
or in combination with, an index on devid?

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@stripped
Web site: www.the-infoshop.com



>> Steve
>>
>That did it!  Thank you.
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1




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