8 ms·

Putting aside the details of how to make it fast on MySql, I always push back on pagination designs that require direct addressing. Pages don't have any semanti

Putting aside the details of how to make it fast on MySql, I always push back on pagination designs that require direct addressing. Pages don't have any semantic meaning for the person clicking on them. Why would they click on page 17? They're not looking for specifically the 170th-179th results of whatever the query is. There are few cases where this is a useful UX and this is for roughly the same reason the DB is bad at it. So my playbook here is to ask if direct addressing is really needed and then, when it isn't, switch to using cursors.

aarondf 5y ago
I actually agree completely with that!
Andys 5y ago
Yes! Additionally, it hurts users when the data is changing beneath them. Rows can appear twice or disappear as you move between pages.
- layer8 5y ago
  As a user, I’d rather live with that possibility than not being able to jump to a page in random-access fashion.
  - pmontra 5y ago
    As a user I like paged results when I sort by a key (maybe a timestamp) and want to look at the records around the one I found. However in a large dataset I always use a search filter. As a developer without a specific requirement if the time is short (it always is) I create a paged navigation using the defaults of the framework I'm using. Then the customer might or might not ask for a search filter. If frameworks defaulted on search filters I'd give customers them first and paged navigation if requested.
  - Andys 5y ago
    OK, but how many pages is too many? I can understand what you're saying for say, 1 to 20 pages, but it doesn't make any sense to me to go to hundreds, or worse, thousands of pages.
postalrat 5y ago
What kind of cursor? Will be there any issue if the time the user waits between the first 10 items and the next time items is a couple hours?
- aarondf 5y ago
  Usually a cursor holds some state about the last record shown to a user on a particular page. The next page should always start directly after the previous one, no matter how much time has elapsed. Most frameworks have some kind of cursor implementation. Laravel's docs do a good job describing theirs: https://laravel.com/docs/8.x/pagination#cursor-pagination https://laravel.com/docs/8.x/pagination#cursor-pagination
  - postalrat 5y ago
    So what's the difference between that and using the id of the row like in the article?
    - aarondf 5y ago
      When you use a cursor, the user has to pass a cursor back to the application to see the next page. So on the first page load, the server will send the records and a cursor that contains the info {'lastId': 32} and when the user requests page two, they'll have to send the cursor {'lastId': 32} so the server knows where to start. In practice, the cursors are usually encoded and appended to the URLs. You can never calculate what page 2 should be, you have to know what the last item on page 1 was, because that dictates where to start on page 2.
Olreich 5y ago
When I look up something twice, having a “go to page” function is sorely lacking in modern apps. I want to flip to page 300, and they never do that anymore.
layer8 5y ago
As a user, I often do something akin to a binary search when direct addressing is available. It can be annoying when it isn’t available, or when there’s not at least a shortcut to skip 10 or 50 pages, or to jump to the end of the search.
- ezekg 5y ago
  But you’re again, not actually using a page number here. What you really need is a different cursor, e.g. a cursor on created_at, to help perform the binary search more efficiently, right? A cursor doesn’t necessarily have to be an ID.
  - layer8 5y ago
    Well, what I do is to jump to a page at a certain ratio between two pages. Of course the UI could allow entering that ratio instead of selecting a numbered page, but I don’t quite see the benefit in that. The internal navigation can in principle be implemented either way (cursor or offset). One requirement is for the size of the result set to be known, at least as an approximation (e.g. like Google does).
- boxed 5y ago
  I see people use this method all the time and I use it myself sometimes but it's 100% of the time a hack around the system not having filtering as far as I can tell.

aarondf 5y ago

I actually agree completely with that!

Andys 5y ago

Yes! Additionally, it hurts users when the data is changing beneath them. Rows can appear twice or disappear as you move between pages.

layer8 5y ago
As a user, I’d rather live with that possibility than not being able to jump to a page in random-access fashion.
- pmontra 5y ago
  As a user I like paged results when I sort by a key (maybe a timestamp) and want to look at the records around the one I found. However in a large dataset I always use a search filter. As a developer without a specific requirement if the time is short (it always is) I create a paged navigation using the defaults of the framework I'm using. Then the customer might or might not ask for a search filter. If frameworks defaulted on search filters I'd give customers them first and paged navigation if requested.
- Andys 5y ago
  OK, but how many pages is too many? I can understand what you're saying for say, 1 to 20 pages, but it doesn't make any sense to me to go to hundreds, or worse, thousands of pages.

postalrat 5y ago

What kind of cursor? Will be there any issue if the time the user waits between the first 10 items and the next time items is a couple hours?

aarondf 5y ago
Usually a cursor holds some state about the last record shown to a user on a particular page. The next page should always start directly after the previous one, no matter how much time has elapsed. Most frameworks have some kind of cursor implementation. Laravel's docs do a good job describing theirs: https://laravel.com/docs/8.x/pagination#cursor-pagination https://laravel.com/docs/8.x/pagination#cursor-pagination
- postalrat 5y ago
  So what's the difference between that and using the id of the row like in the article?
  - aarondf 5y ago
    When you use a cursor, the user has to pass a cursor back to the application to see the next page. So on the first page load, the server will send the records and a cursor that contains the info {'lastId': 32} and when the user requests page two, they'll have to send the cursor {'lastId': 32} so the server knows where to start. In practice, the cursors are usually encoded and appended to the URLs. You can never calculate what page 2 should be, you have to know what the last item on page 1 was, because that dictates where to start on page 2.

Olreich 5y ago

When I look up something twice, having a “go to page” function is sorely lacking in modern apps. I want to flip to page 300, and they never do that anymore.

layer8 5y ago

As a user, I often do something akin to a binary search when direct addressing is available. It can be annoying when it isn’t available, or when there’s not at least a shortcut to skip 10 or 50 pages, or to jump to the end of the search.

ezekg 5y ago
But you’re again, not actually using a page number here. What you really need is a different cursor, e.g. a cursor on created_at, to help perform the binary search more efficiently, right? A cursor doesn’t necessarily have to be an ID.
- layer8 5y ago
  Well, what I do is to jump to a page at a certain ratio between two pages. Of course the UI could allow entering that ratio instead of selecting a numbered page, but I don’t quite see the benefit in that. The internal navigation can in principle be implemented either way (cursor or offset). One requirement is for the size of the result set to be known, at least as an approximation (e.g. like Google does).
boxed 5y ago
I see people use this method all the time and I use it myself sometimes but it's 100% of the time a hack around the system not having filtering as far as I can tell.