Skip navigation links

MySQL Forums :: Performance :: LIMIT, OFFSET too slow on very, very large table


Advanced Search

Re: LIMIT, OFFSET too slow on very, very large table
Posted by: Toa Sty ()
Date: August 25, 2006 03:41AM

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

Options: ReplyQuote


Subject Views Written By Posted
LIMIT, OFFSET too slow on very, very large table 20505 Vincent Theeten 08/25/2006 01:13AM
Re: LIMIT, OFFSET too slow on very, very large table 7444 Toa Sty 08/25/2006 03:41AM


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.