LIMIT, OFFSET too slow on very, very large table

[Vincent Theeten]

Hi all,

We moved from MS SQL to MySQL about 6 months ago; and up to now not a single regret.
In that 6 months we have accumulated a database with close to 3 million records.
In our application we need to be able to select chunks of rows in the middle of the table.

SELECT **Only my PK field** FROM **table** LIMIT 1000 OFFSET 2500000

It worked well with <1 million records, but now takes up to 10 minutes to complete.

I've indexed the columns we use in the select, and I don't see a way to optimize this.

Can it still be done with LIMIT OFFSET with over 3 million records?
Do I need an extra autoincrement field that holds the rownumber and use WHERE?
Or do I need to start partitioning my data? If yes, how?

Any input is welcome.

Cheers,
Vincent

PS: Our DB server is a dual 3Ghz (dual core) with 4Gb of RAM running only MySQL