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 trigger from stamp, or now() if that is appropriate. On Fri, Jan 14, 2011 at 11:52 AM, Bruce Ferrell <bferr...@baywinds.org>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'; > > 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=vegiv...@tuxera.be > > -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
