List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:January 14 2011 4:32pm
Subject:RE: Rewrite SQL to stop table scan
View as plain text  
>-----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




Thread
Rewrite SQL to stop table scanBruce Ferrell14 Jan
  • Re: Rewrite SQL to stop table scanJohan De Meersman14 Jan
  • Re: Rewrite SQL to stop table scanYogesh Kore14 Jan
  • Re: Rewrite SQL to stop table scanYogesh Kore14 Jan
  • Re: Rewrite SQL to stop table scanPeter Brawley14 Jan
  • Re: Rewrite SQL to stop table scanSteve Meyers14 Jan
    • Re: Rewrite SQL to stop table scanBruce Ferrell15 Jan
      • RE: Rewrite SQL to stop table scanJerry Schwartz17 Jan
        • Re: Rewrite SQL to stop table scanSteve Meyers18 Jan
  • RE: Rewrite SQL to stop table scanJerry Schwartz14 Jan