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.