List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:April 5 2001 3:48am
Subject:Re: Limit
View as plain text  
In the last episode (Apr 05), hooker@stripped said:
> > On Wed, Apr 04, 2001 at 05:35:52PM -0600, Mat Murdock wrote:
> > >    If I understand the manual correctly when I do a  "select blah
> > >    from blah where blah = blah order by blah limit 10" Mysql
> > >    finds the first 10 matching records and then sorts those
> > >    records.  How do I have it first sort the database and then
> > >    take the first 10 records?
> > 
> > LIMIT is applied after sorting.  It does what you want already.
> As a matter of interest, if I have 10 million rows and specify :
> "select * from Table order by epoch limit 10;"
> Will all 10 million rows be sorted and the first 10 taken, or will
> the sort be "truncated" so that only the matching 10 rows are
> maitained during the trawl through the table?

Depends on whether you've got a key on epoch or not.  If not, I believe
mysql has to sort the whole thing.  I just did a test on a 1-million
record table, and it took 20 seconds to do a select on an unindexed
table, and 40 seconds to do an order by limit 10.  A "top-n" sort would
have taken around 20 seconds as well.  There are loads of little
optimizations like this can could be added to mysql; most of them only
apply to special cases and might not be worth the effort to code
(unless you buy a support contract. :)

If you do have an index, mysql is smart enough to just pull the first
10 records based on the index.

	Dan Nelson
LimitMat Murdock5 Apr
  • Re: LimitPaul DuBois5 Apr
    • Re: Limithooker5 Apr
      • Re: LimitDan Nelson5 Apr
  • Re: LimitJeremy Zawodny5 Apr
  • Re: LimitDan Nelson5 Apr