Poor performance when using a large Limit offset

[Dan Holdway]

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