design patterns - Redis and MySQL Pagination Strategy

Question

I'm trying to return a list of recent items and have simple pagination. What I'm first doing is making this query

SELECT * FROM table ORDER BY time LIMIT 300;

Then I'm caching it into a Redis sorted set with time as the score. This allows me to paginate efficiently (about 25 a page). What I'm stuck on is what do I do if some user actually reaches the end. Do I just make a massive initial query? Or do I make another query like this when they reach the end.

SELECT * FROM table WHERE time < last_item_time ORDER BY time LIMIT 300;

For the latter strategy, it seems I would have to keep track of the latest item time in the user session key in Redis which is doable but a little annoying. Wondering if there's a better approach since I'm new to backend.

question from:https://stackoverflow.com/questions/66057461/redis-and-mysql-pagination-strategy

Answer 1

Waitting for answers