How correctly to select from the data from the middle of a DB?

Question

MySQL

Hello. There is a simple notes table for a mini-blog: id_author, header, text. And I need to find the second five records of the user who has id_author = 2 (for example).
That is, extract the first 10 records from the table. Then discard the first 5 and give the client only the second 5. What is the best way to do this?

Answer 1

There is a simple notes table for a mini-blog: id_author, header, text. And I need to find the second five user records

In a relational database, a table - also known as a relation - is a set of records, not a list. Those. they are not ordered, and if now the query for the selection gives you them in one order, then tomorrow it will do it in a different order. The record does not store the time it was added anywhere, so the DBMS can shift them as it wishes.
To solve this problem, you need to add a field to store the order of the records. The easiest way is to add the posting time. Also, you may need the id or number field to store the number of the record itself, because now it is not clear whether there can be several entries from one author or not (in theory - maybe, but it is not clear then how you distinguish them. By title?).
Then you might need an index (id_author, postdatetime) to make time-sorted selections efficient.
Now you can choose:

select header, text from notes where id_author = <автор> order by postdatetime limit 5, 5