Slow MySQL limit select

Question

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

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?

Did you already look at what `EXPLAIN` tells you about these queries? Problems with missing indexes etc should be pretty clear in the output of explain. — fvu, Sep 10 '11 at 17:57
Seems like it is using indexes. `id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE quotes index NULL votes 8 NULL 1020 Using index` — Dekken, Sep 10 '11 at 18:11
Well, for some reason my queries are a-bit faster now. the query can now take anywhere from 5 to 20 seconds, which is too long. any ideas? using the method from the post you linked me to, does nothing really. — Dekken, Sep 10 '11 at 18:39

score 0 · Answer 1 · answered Sep 10 '11 at 17:56

I recommend caching the result every hour or so.

I believe the query is heavy and will lagg anyway, but with caching it'll show cached result and update (run query) once a hour.

Correct me, if I'm wrong.

1 Answers1