List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:January 14 2011 11:45am
Subject:Re: Rewrite SQL to stop table scan
View as plain text  
The problem is that you're using a function on your indexed field, which
prevents the index from being used (I'm assuming you have an index on
stamp).

Store stamp directly as unixtime (use a time field) or if that's not an
option, add a column that does - if you want you can autofill it with a
trigger from stamp, or now() if that is appropriate.

On Fri, Jan 14, 2011 at 11:52 AM, 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
>
>


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

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