>-----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 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, > [JS] I didn't see a key on `devid`. That's the only thing I can think of, since putting a key on `stamp` doesn't make sense.
Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com >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=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
