C architecture / selection from the table?

Question

PHP

Hello!
Help, please, to solve the situation: there is a certain table, let's call it timeline , where all the events that happen to the user are written. For example, someone added him as a friend, something happened to his friends and all that kind. The table has a primary key, auto-increment - everything is classical. There is a date_created field - the date the record was created.
Now for the complications. I want to load messages when scrolling. For example, I displayed 10 messages, scrolled to the bottom - 10 more are loaded. Everything is logical and simple until events occur that fall on the same date up to a second. Those. sampling

select * from timeline where date_created<дата_последнего_сообщения_в_ленте LIMIT 10

it’s already normal not to do it - some of the messages will disappear. Sorting by the primary key cannot be done either - in the future, events for past periods can be written to the timeline table and the ID acting as order_id will lose its relevance.
In theory, this could be solved with an end-to-end order_id for each user, but I don’t really want to start another field and recalculate all order_id during some actions.
You can solve the issue as follows - dba.stackexchange.com/questions/23981/mysql-select...
But here we don't like double sorting and variables.
Has anyone had experience with this and can suggest another solution?

Answer 1

Why not use?:

select * from timeline where ORDER BY date_created DESC LIMIT start, 10

Instead of start , substitute 10 * (portion number - 1)

Answer 2

make an index by date_created, it’s better to store it in general in the form of unix_timestamp (or better, create a composite index id & date_created at once)
when you need to load 10 more records, send the parameters (id and date_created) of the last record (i.e. the lowest in the list of events ), and the query will look like:

SELECT * FROM timeline WHERE id > %s AND date_created <= %s ORDER BY date_created DESC, id ASC LIMIT 10

i.e. the record with
the passed id will be skipped and 10 more records will be selected that you need, and which come directly after the record with the id number. request, then this entry will be shown only when the page with events is reloaded.

Answer 3

Do you have PK is autoincrement? If yes, then like this

select * from timeline where id>id_последнего_сообщения_в_ленте LIMIT 10