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 |
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
