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,
>>
>> 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
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
>-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 scan. It does in fact do a ful
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
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
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
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
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
