List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:June 20 2002 3:17pm
Subject:Re: 3.23.51 LIMIT efficiency problem
View as plain text  
In the last episode (Jun 19), Mark Hazen said:
> I am using MySQL 3.23.51 on Linux.  I have a fixed-length table with 45
> million rows.  It appears MySQL has an efficiency issue with the LIMIT
> command.  When I run SELECT a FROM b LIMIT 0, 1000000   <-- that's one
> million
> The query finishes almost immediately.
> 
> But when I run 20 million rows into the dataset, it really slows down.
> (pretend the commas aren't there)
> SELECT a FROM b LIMIT 20,000,000, 1,000,000
> 
> Why would it slow down?  We're dealing with a fixed-length table here.  This
> is just mathematics to figure out where row 20,000,000 is.

Without an ORDER BY, there is no "row 20,000,000".  SQL results are
unordered sets, so mysql has to walk through 20M records before it can
hand you the 1M you want.  Ordering by your primary key should make it
a lot faster, as mysql should be able to do an index walk to quickly
find the correct starting point.

-- 
	Dan Nelson
	dnelson@stripped
Thread
How to convert an Access 2000 Database on PC to a MySQL onUnix PlatformLiqiang Feng19 Jun
  • Re: How to convert an Access 2000 Database on PC to a MySQL on UnixPlatformOliver Vecernik19 Jun
    • RE: How to convert an Access 2000 Database on PC to a MySQL on UnixPlatformmichael johnson20 Jun
  • 3.23.51 LIMIT efficiency problemMark Hazen20 Jun
    • Re: 3.23.51 LIMIT efficiency problemDan Nelson20 Jun
    • Re: 3.23.51 LIMIT efficiency problemcentral20 Jun
    • Re: 3.23.51 LIMIT efficiency problemBenjamin Pflugmann20 Jun