Pagination

from the Artful Common Queries page

Suppose you have a phone book of names, addresses, etc. You are displaying 20 rows per page, you're on page 100, and you want to display page 99. How do you do this knowing only what page you are on? Assuming...
  • 1-based page numbers
  • you are on page P
  • each page shows N rows
then the general formula for translating a 1-based page number into a first LIMIT argument is ... 
 MAX(0,P-1) * N
which for the 99th 20-row page evaluates to 1960, and the second argument to LIMIT is just N, so to see page 99, write... 
 
 SELECT ... LIMIT (1960, N);
The trouble with this is scaling. MySQL doesn't optimise LIMIT at all well. SELECT ... LIMIT 1000000,20 will unfortunately retrieve not just the twenty rows starting at the millionth row; it will retrieve a million and twenty rows before it shows you the 20 you asked for! The bigger the result, the longer LIMIT takes.

What's the alternative? Build pagination logic into the WHERE clause, and ensure that there is a covering index for the paginating column. On a table of 100,000 indexed random integers, SELECT ... WHERE ... for the last 20 integers in the table is twice as fast as the comparable LIMIT query. With a million integers, it's more than 500 times faster!

If your interface calls for showing only 20 rows per page on a given order, retrieve the twenty rows, plus the row just before the set if it exists, plus the next row after those twenty if it exists. When the user clicks the Previous Page button, adjust the WHERE clause to specify rows where the key value is <= the row just before the current set and ORDER BY the current index DESC LIMIT 20; likewise when the user clicks the Next Page button, have the WHERE clause specify key values >= that of the row just after the set, and ORDER BY the current index ASC LIMIT 20.

For more detail see https://mysql.rjweb.org/doc.php/pagination.

Last updated 11 Apr 2020