List:General Discussion« Previous MessageNext Message »
From:Yogesh Kore Date:January 14 2011 12:40pm
Subject:Re: Rewrite SQL to stop table scan
View as plain text  
can you send DDL of the table?

On Fri, Jan 14, 2011 at 4:22 PM, Bruce Ferrell <bferrell@stripped>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=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