What to use instead of LIMIT x, y if BETWEEN is not capable? Pagination
I have 10k forum topics. I run this query:
SELECT *
FROM `topics`
WHERE category_id = '2'
ORDER BY `last_message` DESC
LIMIT 2990, 10
This checks all rows! All rows! Even after adding index to last_message
. last_message
is a time() and I want to order all messages by that descending. So开发者_StackOverflow社区, what do you suggest me? Without BETWEEN, because it can't be done with that, unfortunately.
Since you have a filtering condition on category_id
, an index on last_message
most probably won't help you much.
The query will have to traverse the index and filter the records for category_id = 2
. Since traversing an index is more expensive than scanning the table, a filesort
can be a more efficient solution, and MySQL
may prefer it over the index scan (that's most probably is what is happening in your case).
Also, as @OMG Ponies pointed out, MySQL
is not capable of doing late row lookups.
Assuming that your PRIMARY KEY
column is called id
, you need to create an index on (category_id, last_message, id)
and rewrite your query as follows:
SELECT t.*
FROM (
SELECT id
FROM topics
WHERE category_id = 2
ORDER BY
category_id DESC, last_message DESC, id DESC
LIMIT 2990, 10
) q
JOIN topics t
ON t.id = q.id
To get MySQL to use late row lookups, use:
SELECT t.*
FROM TOPICS t
JOIN (SELECT *
FROM `topics`
WHERE category_id = '2'
ORDER BY `last_message` DESC
LIMIT 2990, 10) x ON x.topic_id = t.topic_id
ORDER BY t.topic_id
Without TOPICS
column details, I have to assume there is an id column that is auto_increment for the table...
You can read more about the behavior in this article.
精彩评论