I am working on a small website using Java where the user posts are stored in MySQL, I am getting the posts 5 at a time (5 rows using LIMIT 5) by most recent one (using timestamp DESC) and send it to the front-end .

The problem I am trying to solve is to be able to fetch the next 5 that are not the same rows that were fetched first...and so on. So I need to find away to keep track of which rows were selected and fetched in the last query and avoid getting them again.

The idea is that when the user scrolls down to the end, AJAX will send a request to servlet and the servlet will get the next 5 rows from MySQL.

I've been trying to find away by experimenting with the database as well as some online search, could not find a way so far.


Although you can track which rows were selected last using MySQL Enterprise Audit, this would be a little like using a Saturn V rocket to go to the corner store... The cost would be ridiculously high for such a small function.

Since you are ordering rows by timestamp, I am assuming your timestamp value is unique. Wouldn't adding a select clause like where <mytimestampcolumn> < [smallest timestamp column value from last result set]; take care of the requirement?

This would have the additional benefit of making your application scale better relative to other alternatives, such as retrieving the complete result into a cursor and having the servlet fetch the result set 5 rows at a time.

| improve this answer | |

Remember where you left off.

More discussion: http://mysql.rjweb.org/doc.php/pagination

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.