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
>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.
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.
Select a from b where rcd_id > 1000000 and rcd_id < 20000000
Select a from b where rcd_id > 1000000 limit 1000000
Because you're not using an offset, it should be quite fast. :)