List:General Discussion« Previous MessageNext Message »
From:Bruce Ferrell Date:January 14 2011 10:52am
Subject:Rewrite SQL to stop table scan
View as plain text  
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 |


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