Rewrite SQL to stop table scan

am 14.01.2011 11:52:51 von Bruce Ferrell

Re: Rewrite SQL to stop table scan

am 14.01.2011 12:45:01 von Johan De Meersman

--0015174be7b4e834120499ccf6a7
Content-Type: text/plain; charset=ISO-8859-1

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 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=vegivamp@tuxera.be
>
>

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

--0015174be7b4e834120499ccf6a7--

Re: Rewrite SQL to stop table scan

am 14.01.2011 13:40:10 von Yogesh Kore

Re: Rewrite SQL to stop table scan

am 14.01.2011 13:43:27 von Yogesh Kore

Re: Rewrite SQL to stop table scan

am 14.01.2011 14:02:56 von Peter Brawley

Re: Rewrite SQL to stop table scan

am 14.01.2011 17:19:28 von Steve Meyers

On 1/14/11 3:52 AM, Bruce Ferrell wrote:
> select count(*) as count
> from alerts where (unix_timestamp(stamp)> (unix_timestamp(now()) -
> '300' ) )
> and devid = '244';
>

Bruce -

The problem is that the index is useless, because you're running a
function on the timestamp. What you want is this:

SELECT COUNT(*) AS num FROM alerts WHERE stamp > DATE_SUB(NOW(),
interval 300 second) AND devid=244;

With this query, MySQL will run DATE_SUB() once, and then use the index
on stamp (which I assume you have) to narrow down the result set.

Steve

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Rewrite SQL to stop table scan

am 14.01.2011 17:32:58 von Jerry Schwartz

>-----Original Message-----
>From: Bruce Ferrell [mailto:bferrell@baywinds.org]
>Sent: Friday, January 14, 2011 5:53 AM
>To: mysql@lists.mysql.com
>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@gii.co.jp
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=jerry@gii.co.jp

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Rewrite SQL to stop table scan

am 15.01.2011 04:25:12 von Bruce Ferrell

On 01/14/2011 08:19 AM, Steve Meyers wrote:
> On 1/14/11 3:52 AM, Bruce Ferrell wrote:
>> select count(*) as count
>> from alerts where (unix_timestamp(stamp)> (unix_timestamp(now()) -
>> '300' ) )
>> and devid = '244';
>>
>
> Bruce -
>
> The problem is that the index is useless, because you're running a
> function on the timestamp. What you want is this:
>
> SELECT COUNT(*) AS num FROM alerts WHERE stamp > DATE_SUB(NOW(),
> interval 300 second) AND devid=244;
>
> With this query, MySQL will run DATE_SUB() once, and then use the
> index on stamp (which I assume you have) to narrow down the result set.
>
> Steve
>
That did it! Thank you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Rewrite SQL to stop table scan

am 17.01.2011 17:52:36 von Jerry Schwartz

>>
>> SELECT COUNT(*) AS num FROM alerts WHERE stamp > DATE_SUB(NOW(),
>> interval 300 second) AND devid=244;
>>
>> With this query, MySQL will run DATE_SUB() once, and then use the
>> index on stamp (which I assume you have) to narrow down the result set.
>>
[JS] I don't understand how an index on a timestamp would help. Theoretically,
each record could have a unique value for the timestamp; so the index would
have an entry for each record. Would MySQL really use that in preference to,
or in combination with, an index on devid?

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com

>> Steve
>>
>That did it! Thank you.
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=jerry@gii.co.jp

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Rewrite SQL to stop table scan

am 18.01.2011 02:55:40 von Steve Meyers

On 1/17/11 9:52 AM, Jerry Schwartz wrote:
> [JS] I don't understand how an index on a timestamp would help. Theoretically,
> each record could have a unique value for the timestamp; so the index would
> have an entry for each record. Would MySQL really use that in preference to,
> or in combination with, an index on devid?

You are correct. The ideal index would be across (devid, stamp).

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org