5 ms·
The downside is that OFFSET is slow if you have a lot of items, like on a forum. At which point you could assign each post in a topic an indexed `position` col
by danneu 11y ago
The downside is that OFFSET is slow if you have a lot of items, like on a forum.
At which point you could assign each post in a topic an indexed `position` column that always increases from 0 within that topic, which lets you jump to arbitrary pages and parameterize perPage.
fromPos = (page - 1) * perPage
SELECT *
FROM posts
WHERE topic_id = $1
AND position >= fromPos
AND position < fromPos + perPage
ORDER BY id
For simpler needs, your "Next" button could load items that come after the last ID on the current page but with a LIMIT.
<ul>
{% for item in items %}
<li>{{ item }}</li>
{% endfor %}
</ul>
<a href="/items?afterId={{ items[items.length - 1].id }}">Next</a>
SELECT *
FROM items
WHERE id > $afterId
LIMIT $perPage
That's fast and really easy.
- lox 11y agoThis sounds like a really premature optimization to me. Let the DB do it's job unless you have an explain trace showing it's slow for your workload. Trying to do things like sequence columns adds complexity, when this is often handled by the engine.
- lucasmullens 11y agoYou need this if it's for a feed that gets frequent updates. Imagine a paginated Twitter feed, and the user clicks a link with ?page=3, and suddenly there's a new tweet, causing page 3 to have a tweet the user saw on page 2. Using afterId solves that.
- danneu 11y agoOFFSET is not "handled by the engine". It heapsorts the entire dataset before you offset to a page. The top-level comment is asking for good ways to paginate. OFFSET works on localhost and with hundreds of rows. My solution is one that works in production once OFFSET fails you. For me, it was day 2.
- seeing 11y agoIt was day 2 for you, with the specific engine you used. You don't need to heapsort the entire dataset before you offset to a page, and there are engines capable of doing this.
- tripzilch 11y agoOr they could be speaking from experience, because back in the day when webforums where the place to make online communities, as they grew beyond a certain size this sort of thing became THE bottleneck, especially for large threads. I empirically know this was an issue back then. I also agree it's something a DB engine should handle, and I do hope that databases do a better job today. So instead of calling out "premature optimization", maybe instead come with a practical example of a DB engine that properly handles this?