What to use instead of LIMIT x, y if BETWEEN is not capable? Pagination.

Question

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, what do you suggest me? Without BETWEEN, because it can't be done with that, unfortunately.

Answer 1

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.

Answer 2

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