6 ms·
It's really easy to paginate data with SQL - just use LIMIT and OFFSET. limit = page_size offset = (current_page - 1) * limit For example, let's say you h
by fbonetti 10y ago
It's really easy to paginate data with SQL - just use LIMIT and OFFSET.
limit = page_size
offset = (current_page - 1) * limit
For example, let's say you have a table with 1000 products in it, and you want to display page 3 with a page size of 50. Here's how you would write it:
SELECT *
FROM products
LIMIT 50
OFFSET 100
- pmarreck 10y agoAnd if the data frequently changes during pagination?
- objclxt 10y agoThat's why it's often better to use a cursor in your client-facing API (or a pseudo-cursor that maps to something you have in your database), rather than passing the limit/offset through.
- danneu 10y agoThe 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 10y 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 10y 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 10y 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 10y 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 10y 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?
- momo-reina 10y agoI just wrote about doing it this way yesterday. http://www.mozartreina.com/ http://www.mozartreina.com/
- mceachen 10y agoYou should at least reference the fact that with MySQL, the server has to step through OFFSET result rows before it can start streaming you data. This can get surprisingly expensive, surprisingly quickly. See http://stackoverflow.com/questions/4481388/why-does-mysql-higher-limit-offset-slow-the-query-down http://stackoverflow.com/questions/4481388/why-does-mysql-hi...