Re: LIMIT, OFFSET too slow on very, very large table

[Toa Sty]

Hi Vincent,

If you use LIMIT and OFFSET like this, the way it works is that MySQL processes the query as if you'd said (in your case) LIMIT 2501000 then just throws away (i.e. doesn't return to you) the first 2500000 rows.

It's really not an efficient way to do things at all.


If you have a condition that you can specify in a WHERE clause that will use an index to identify the required rows then you'll see really huge speedups. (So yeah using an auto increment rownumber col and WHERE would work very nicely)


I'd really suggest doing it that way, but as an aside you might find that the huge drop in speed you've seen with your current method is due to your index not fitting in memory any more so having to hit disk. Maybe as a temporary measure increase your key_buffer to something big enough if it's not already.

You'll see orders of magnitude better performance if you use your suggested method of an auto increment col and a WHERE though! :)

Toasty