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
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
