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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
