MySQL Forums
Forum List  »  Performance

Poor performance when using a large Limit offset
Posted by: Dan Holdway
Date: January 22, 2005 08:44PM

Hi,

I have a problem that I hope someone can help me with;

When I execute the following statement against a reasonably large table (250k records), it is pretty quick (2 secs) as I have an index on field1, field2;

Select *
From big_table
Where field1 = 1
order by field2
limit 100

But if I introduce a large offset on the limit, it takes ages (over a minute) to return the records;

Select *
From big_table
Where field1 = 1
order by field2
limit 10000, 100

This makes no sense to me as I would have assumed that it would just use the index to jump to the block of records with ease, but it seems not to.

Can someone please help me with this, because it's got me stumped! :(

Thanks

Options: ReplyQuote


Subject
Views
Written By
Posted
Poor performance when using a large Limit offset
7917
January 22, 2005 08:44PM


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.