Re: Rewrite SQL to stop table scan

2011-01-17 Thread Steve Meyers
On 1/17/11 9:52 AM, Jerry Schwartz wrote: [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,

RE: Rewrite SQL to stop table scan

2011-01-17 Thread Jerry Schwartz
>> >> 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 o

Re: Rewrite SQL to stop table scan

2011-01-14 Thread 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

RE: Rewrite SQL to stop table scan

2011-01-14 Thread Jerry Schwartz
>-Original Message- >From: Bruce Ferrell [mailto:bferr...@baywinds.org] >Sent: Friday, January 14, 2011 5:53 AM >To: mysql@lists.mysql.com >Subject: Rewrite SQL to stop table scan > >How would you rewrite the following SQL so that is doesn't do a full >table

Re: Rewrite SQL to stop table scan

2011-01-14 Thread 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: S

Re: Rewrite SQL to stop table scan

2011-01-14 Thread Peter Brawley
On 1/14/2011 4:52 AM, Bruce Ferrell wrote: How would you rewrite the following SQL so that is doesn't do a full table scan. It does in fact do a full scan in spite of the time clause. It's been making me nuts for months. select count(*) as count from alerts where (unix_timestamp(stamp)> (uni

Re: Rewrite SQL to stop table scan

2011-01-14 Thread Yogesh Kore
Hey, Try making `id` as primary key. That will keep data in sorted manner and scan will look only the data required in-spite of full table scan. On Fri, Jan 14, 2011 at 4:22 PM, Bruce Ferrell wrote: > How would you rewrite the following SQL so that is doesn't do a full > table scan. It does in

Re: Rewrite SQL to stop table scan

2011-01-14 Thread Yogesh Kore
can you send DDL of the table? On Fri, Jan 14, 2011 at 4:22 PM, Bruce Ferrell wrote: > How would you rewrite the following SQL so that is doesn't do a full > table scan. It does in fact do a full scan in spite of the time > clause. It's been > making me nuts for months. > > select count(*) as

Re: Rewrite SQL to stop table scan

2011-01-14 Thread Johan De Meersman
The problem is that you're using a function on your indexed field, which prevents the index from being used (I'm assuming you have an index on stamp). Store stamp directly as unixtime (use a time field) or if that's not an option, add a column that does - if you want you can autofill it with a tri

Rewrite SQL to stop table scan

2011-01-14 Thread Bruce Ferrell
How would you rewrite the following SQL so that is doesn't do a full table scan. It does in fact do a full scan in spite of the time clause. It's been making me nuts for months. select count(*) as count from alerts where (unix_timestamp(stamp) > (unix_timestamp(now()) - '300' ) ) and devid = '