How to make quick pagination sorted by date?

Question

MySQL

Greetings.
You need to do a quick pagination on the comments table on the site by date
There are only 450 thousand comments in the
table Table of the form
id - int|comment - varchar|dt - date
Page selection

SELECT * FROM comment order by dt desc LIMIT 50 OFFSET {page*50}

On the first pages it all works fast, but when the offset becomes large, everything starts to shamelessly slow down There is
an index on dt, but it does not get into explain
How to make such a paganization fast? Taking into account the fact that comments are sometimes deleted, and just doing SELECT * FROM comment WHERE id > {page*50} LIMIT 50 will not work?
It remains to keep the pagination pages and ids for each page in some kind of cache?
Or have I missed something?

Answer 1

offhand
Where LAST_COMMENT_INDEX is the index of the last comment from the previous page

Answer 2

mysql on large volumes can ignore string indexes (yes, datetime index is string in general). There are 2 options:
- try to make an index on a separate field with unix_timestamp (with int everything should work in theory)
- put all the fields that you receive in the query into the index (then you will not leave him a choice, but the index will be much more heavy)

Answer 3

hack