From: Andy Wallace
Date: September 5 2012 9:48pm
Subject: Re: Understanding Slow Query Log
List-Archive: http://lists.mysql.com/mysql/228123
Message-Id: <5047C899.1010109@ihouseweb.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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:awallace@stripped]
>> Sent: Wednesday, September 05, 2012 2:05 PM
>> To: mysql@stripped
>> 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.
>> awallace@stripped
>> (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.
awallace@stripped
(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