Slow MySQL limit select

Question

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?

Hi Col. Shrapnel. If votes is a column, can you please explain why an index would not help. Thanks! — Michael Durrant, Sep 11 '11 at 12:01
Why do you say that @Col. Shrapnel? An index will allow MySQL to avoid sorting the entire table to satisfy the `LIMIT` clause. — Michael Mior, Sep 11 '11 at 12:02
Why do you people keep closing this as an exact duplicate, what is the point? do I need to bump that old topic back to life or something? — Dekken, Sep 11 '11 at 13:44
@Dekken: If you don't have enough memory to handle that mammoth index, then you have nothing at hand. If you can somehow use a `WHERE` clause, it will help a bit, otherwise look for a different db solution. — Shef, Sep 11 '11 at 13:55
I have a dedicated server with around 2gigs of memory, wouldn't that be enough? — Dekken, Sep 11 '11 at 14:22
Have you tried adding id as second column on your votes index? — Klas Lindbäck, Sep 12 '11 at 08:05

score 2 · Answer 1 · answered Sep 11 '11 at 11:53

The problem with this query is that it first needs to find out what the top 100000 are, before it can fetch 100000-1000020.

In our case we were dealing with a gallery and pages, and we simply blocked access after x pages; thus preventing the problem altogether.

If you don't already have an index on votes, start there; but I would guess you have already done that (I hope so!). Is there some other criteria you can sort on? Can you sort descending instead? Are there other ways to reduce the number of rows from 100000 to something much lower?

If all else fails, you're gonna have to find other solutions; You might need to denormalize and pregenerate what you expect to return from that query. Maybe you need to cache all the results and have a warm-up script running.

Hoping this will help you on your way..

I don't think there is really a way to reduce the amount of rows or split them into different tables. each post has 9 columns, and there is no criteria to sort them to different tables by. — Dekken, Sep 11 '11 at 12:24

Slow MySQL limit select

1 Answers1