Possible Duplicate:
Why does MYSQL higher LIMIT offset slow the query down?

My other thread was closed due to it being a duplicate, but the duplicate, did not help me at all so I do not see a reason for closing this one also!

I have a page and I would like to sort posts in it from top to bottom by the amount of votes each post has.

I have millions of records, and the query gets really slow. it takes few good minutes to get the results.

This is fine:

SELECT `id` FROM `table` ORDER BY `votes` LIMIT 0,20;

This will take ages:

SELECT `id` FROM `table` ORDER BY `votes` LIMIT 100000,20;

I am not even talking about setting it to 1,000,000 and more.

Any ideas on how to make this faster? I tried using the code from Why does MYSQL higher LIMIT offset slow the query down? But it does not help either. Do you guys have any suggestions on how I can achieve the same result but maybe using PHP? or some better query?

Thanks

Originally asked by: Dekken on Stack Overflow