>-----Original Message-----
>From: Bruce Ferrell [mailto:bferrell@stripped]
>Sent: Friday, January 14, 2011 5:53 AM
>To: mysql@stripped
>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: jerry@stripped
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=1
