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)> (unix_timestamp(now()) -
> '300' ) )
> and devid = '244';
How about indexing on stamp, devid, then writing ... where stamp >
current_timestamp ...?
PB
------
> Thanks in advance,
>
> Bruce
>
> P.S.
>
> I've tried it this way:
>
> select count(*) as count from alerts where ((unix_timestamp(stamp)>
> (unix_timestamp(now()) - '300' ) )) and devid = '244';
>
> and explain always says this:
>
> +--+---------------+------+---------+------+---------+-------------+
> | id | select_type | table | type | possible_keys | key | key_len |
> ref | rows | Extra |
>
> +----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
> | 1 | SIMPLE | alerts | ALL | NULL | NULL | NULL
> |NULL | 2041284 | Using where |
>
> +----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
>
> And it's structured this way:
>
> | Field | Type | Null | Key | Default
> | Extra
> | id | varchar(60) | NO | MUL |
> |
> | stamp | timestamp | NO | MUL |
> CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
>
>
>
