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 <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=yogeshk...@gmail.com > >
