Re: Efficient MySQL for pagination of large amounts of data on website
Posted by:
Rick James
Date: October 08, 2010 08:34AM
1. "able to jump to any page at any time" --
* If you have 10 pages the efficiency discussions are not critical.
* If you have 1000 pages, what is the user interface? Are you going to have 1000 links on one page to get to the others? Are you going to require the user type in a number?
OK, let's say the user types in a number. Pretty soon, he will bookmark page 765. Next week he clicks on that bookmark. Guess what? Nothing on that page is the same! Why? Because (in most applications) pagination is a moving target.
OK, you say it is not a moving target; you never delete old stuff, and you only add new stuff. This works only if you turn around the page numbers -- oldest is page #1. Current page is #1234.
Well, if the data is that rigid, why not have sequence numbers (either consecutive, or chronological, or based on a timestamp. Then the UI is "get me all items starting with sequence #nnn. No page numbers needed.
2. Your idea of a pre-built index of pages is great. But only if the list is not growing and shrinking.
If you build it when needed, how long do you keep it?
How do you explain to the user that new items won't show up on page 1 until you rebuild the index?
3. You final idea of having both the page number and the last_id available for computation is good. This provides "go to next page" with the same performance. It also allows an adequate way to go to any later page, using OFFSET. If you also had first_id, you could play games going backward -- decide whether to back up from the current page (pg 10 -> pg 7) or start over from the beginning (pg 10 -> pg 3).
But, again, you are probably assuming the user types in a page number.
4. Many UIs have something like this; and it is usually 'good enough'. Example when you are on page 21:
First .. 17 18 19 20 (21) 22 23 24 25 .. Last
5. Please further describe the use case where you would like to provide "Go To Page ___" to a user on a list that is over 100 pages long. In particular, what kinds of data is it (blog, news, directory, catalog, etc) and what is the ordering (time, sequence number, part number, alphabetical name, etc)
6.
Kid, in doctor's office: "It hurts when I do this."
Doctor: "Don't do that."