Thanks, Rick - definitely something to think about. I've been
troubled by the pagination stuff in our code. This looks like
something I can definitely use!
andy
On 9/5/12 2:40 PM, Rick James wrote:
Remember where you "left off".
Your "Next" button now says something like ?page=5&size=50
When you get there, you are doing something like SELECT ... ORDER BY ... LIMIT
250, 50
Instead...
Make it say ?after_id=12345&size=50
and then do SELECT ... WHERE id > 12345 ORDER BY ... LIMIT 51
With 51, you get 3 things:
* the 50 items (or fewer) for the page
* a clue that there will be a "Next" page
* the id of the first item for that Next page
'Exercises for the reader':
* 'Prev'
* each of the next 5
* each of the previous 5
* go to last page
* go to first page
* Knowing whether to have those links or 'gray them out'.
A sample UI layout (you've probably seen web pages like this):
GoTo Page [1] ... [13] [14] 15 [16] [17] ... [last]
Where
* [] represents a link.
* You are currently (for this example) on page 15
* It is showing you only the Next/Prev 2 pages.
I have encountered multiple cases where a "crawler" (eg, search engine) brought a site to
its knees because of "pagination via OFFSET".
"Pagination via OFFSET" is Order(N) to fetch a page; Order(N*N) to scan the
entire list. The first page takes 1 unit of effort. The second takes 2; etc. By the
time the entire list has been paged through, about N*N/2 units of work have been done.
My technique is Order(1) for a page, Order(N) for a complete scan.
N is the number of pages. Some implementations have more than 10,000 pages.
10,000 * 10,000 = 100 million !
-----Original Message-----
From: Andy Wallace [mailto:awall...@ihouseweb.com]
Sent: Wednesday, September 05, 2012 2:05 PM
To: mysql@lists.mysql.com
Subject: Re: Understanding Slow Query Log
Ok, this raises a question for me - what's a better way to do
pagination?
On 9/5/12 2:02 PM, Rick James wrote:
* LIMIT 0, 50 -- are you doing "pagination" via OFFSET? Bad idea.
--
Andy Wallace
iHOUSEweb, Inc.
awall...@ihouseweb.com
(866) 645-7700 ext 219
--
"Sometimes it pays to stay in bed on Monday, rather than spending the
rest of the week debugging Monday's code."
- Christopher Thompson
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
--
Andy Wallace
iHOUSEweb, Inc.
awall...@ihouseweb.com
(866) 645-7700 ext 219
--
"Sometimes it pays to stay in bed on Monday, rather than spending the rest of the
week debugging Monday's code."
- Christopher Thompson
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
