Re: LIMIT, OFFSET too slow on very, very large table
Date: August 25, 2006 03:41AM
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! :)
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.