| LIMIT starting at high row count very slow [message #3172] |
Wed, 11 June 2008 13:40  |
 jcott28
Messages: 2
Registered: June 2008 |
Junior Member |
|
|
Im using MySQL 5.0.51 on RedHat enterprise 5 with 8 GB of RAM and 7200 RPM SATA Hard drives (RAid 5).
I have a table with about 250 million rows in it.
When I do a select, such as:
SELECT col1, col2, col3 FROM large_table LIMIT 0,500000
it returns pretty quick (about 5 seconds).
When I do:
SELECT col1, col2, col3 FROM large_table LIMIT 40000000,500000
It takes about 2 minutes.
Needless to say, this isn't going to be very good for my pagination through the data.
Is there some sort of setting I need to adjust in order to have the limit statement work quicker. I'm a bit confused why it would take SO much longer when it's returning the same number of rows and just starting in a place further along the path. The seek time on these hard drives can't be THAT slow.
Any thoughts or help would be greatly appreciated.
Thanks,
Jeff
|
|
|
|
| Re: LIMIT starting at high row count very slow [message #3181 is a reply to message #3175 ] |
Fri, 13 June 2008 11:13  |
jcott28
Messages: 2
Registered: June 2008 |
Junior Member |
|
|
I guess I had assumed that limit didn't scan the first 40,000,000 rows and would just jump up to the 40,000,000th record since the column is indexed.
The best workaround I could figure out was to create a primary key identity field and then I could do a select where the pk between 40,000,000 and 40,500,000. That returns almost instantaneously.
|
|
|
Members
Search
Help
Register
Login
Home
