List:General Discussion« Previous MessageNext Message »
From:central Date:June 20 2002 4:27pm
Subject:Re: 3.23.51 LIMIT efficiency problem
View as plain text  
At 09:03 PM 6/19/2002, you wrote:
>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.
>
>Confused,
>Mark

Mark,
         The slow down is caused by your offset in your limit clause. MySQL 
still has to traverse the offset # of rows (otherwise how would it know 
when to start fetching rows?). Rather than doing an offset, why not do a 
rcd_id range? This will use the index and will be quite fast.

Example:
Select a from b where rcd_id > 1000000 and rcd_id < 20000000

or

Select a from b where rcd_id > 1000000 limit 1000000

Because you're not using an offset, it should be quite fast. :)

Mike


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