Reputation: 761
With a message archive application, I want to paginate through a table of message subjects efficiently. This is easy enough if I sort just by date. I can remember the first and last dates shown on the page and then query for a set of messages greater or less than those. But the application allows some messages to be pinned; they should always appear before other messages, regardless of whether the messages are sorted by ascending or descending date. A message is pinned if a bool is togged true on the record.
What's the best way to efficiently page through this? Assume the table is very large, and messages can be added and deleted at will. We're using Postgres if that makes a difference.
EDIT: I know how to ORDER BY to get what I want. The question is how best to paginate through the results (ie not by using OFFSET).
Upvotes: 1
Views: 651
Reputation: 248295
Sort the messages with an ORDER BY
clause and get the first 50 entries like that:
ORDER BY NOT pinned, create_timestamp LIMIT 50
Remember the last (NOT pinned, create_timestamp)
combination.
To retrieve the next page, use
WHERE (NOT pinned, create_timestamp) > (last_notpinned, last_createts)
ORDER BY NOT pinned, create_timestamp LIMIT 50
and so on for the following pages.
You'll need an index like
CREATE INDEX ON tablename ((NOT pinned), create_timestamp);
to make all this efficient.
Upvotes: 2