LIMIT starting at high row count very slow

MySQL & MariaDB Other MySQL® Questions
1

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

2

What do you expect?
Using LIMIT 40000000,500000
is doing nothing else then going through the first 40000000 rows. So youve got to wait.
Of course you can use an index an something like between.

3

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.