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.