From: Rick James
Date: September 5 2012 9:40pm
Subject: RE: Understanding Slow Query Log
List-Archive: http://lists.mysql.com/mysql/228122
Message-Id: <2E7DD7ADE53B044C8C8BCD9C5829E1EB148B88942F@SP2-EX07VS01.ds.corp.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Remember where you "left off".

Your "Next" button now says something like ?page=3D5&size=3D50
When you get there, you are doing something like SELECT ... ORDER BY ... LI=
MIT 250, 50

Instead...
Make it say ?after_id=3D12345&size=3D50
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) bro=
ught 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; e=
tc.  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 =3D 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
>=20
> Ok, this raises a question for me - what's a better way to do
> pagination?
>=20
> On 9/5/12 2:02 PM, Rick James wrote:
> > * LIMIT 0, 50 -- are you doing "pagination" via OFFSET?  Bad idea.
>=20
>=20
>=20
> --
> 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
>=20
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql