ORDER BY LIMIT issue

With the following query if I it returns 2 results it's fast .04s, if
it has less results than the limit it takes 1minute.

Query:
select * from hub_dailies_sp where active='1' and date='2010-08-04'
order by id desc LIMIT 2;

Show create table:
http://pastebin.org/447171

27,000 rows in table/view

Can anyone shed any light?

Cheers,
Kristian

--
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: ORDER BY LIMIT issue

Hi,

> With the following query if I it returns 2 results it's fast .04s, if
> it has less results than the limit it takes 1minute.
>
> Query:
> select * from hub_dailies_sp where active='1' and date='2010-08-04'
> order by id desc LIMIT 2;
>
> Show create table:
> http://pastebin.org/447171
>
> 27,000 rows in table/view
>
> Can anyone shed any light?

Isn't it so that it firstly order the rows by id (index'ed?) and then scan
it to pick the rows which satisfy the where clause?

It stops when the result reaches the limit, otherwise scans the whole (27,
000 rows scan).

Then the response time with 2 rows limit by 2 can really depend. If the ma
tching 2 rows reside at the very beginning of the id ordered result, it ca
n be very fast (2 rows scan), but if they are located at the end, it can t
ake as long as the full table scan.

Correct me if I'm wrong.

Regards,
Eigo

> Cheers,
> Kristian
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=eigom@pop01.odn.ne
> .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: ORDER BY LIMIT issue

> Isn't it so that it firstly order the rows by id (index'ed?) and then sca=
n
> =A0it to pick the rows which satisfy the where clause?
>
> It stops when the result reaches the limit, otherwise scans the whole (27=
,
> 000 rows scan).
>
> Then the response time with 2 rows limit by 2 can really depend. If the m=
a
> tching 2 rows reside at the very beginning of the id ordered result, it c=
a
> n be very fast (2 rows scan), but if they are located at the end, it can =
t
> ake as long as the full table scan.
>
> Correct me if I'm wrong.

Thanks for the response. I've had a deeper look at it appears an
issue with the
optimizer... I think I need to create some more Indexes.

-Kristian

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

RE: ORDER BY LIMIT issue

Because you are sorting the results, the LIMIT clause has to be applied after
all of the eligible rows have been retrieved. There shouldn't be a big
difference between 2 and 3, but there would be between 2 and 20000.

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

>-----Original Message-----
>From: Kristian Davies [mailto:kristian.davies@gmail.com]
>Sent: Wednesday, August 04, 2010 5:03 AM
>To: mysql@lists.mysql.com
>Subject: ORDER BY LIMIT issue
>
>With the following query if I it returns 2 results it's fast .04s, if
>it has less results than the limit it takes 1minute.
>
>Query:
> select * from hub_dailies_sp where active='1' and date='2010-08-04'
>order by id desc LIMIT 2;
>
>Show create table:
>http://pastebin.org/447171
>
>27,000 rows in table/view
>
>Can anyone shed any light?
>
>Cheers,
>Kristian
>
>--
>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