0

I found an article speak about MySQL performance tips, and here is one of their tips:

MySQL performance tip No. 7: Watch out for pagination queries

On the query side, instead of using LIMIT with offset, you can select one more row than you need, and when the user clicks the "next page" link, you can designate that final row as the starting point for the next set of results. For example, if the user viewed a page with rows 101 through 120, you would select row 121 as well; to render the next page, you'd query the server for rows greater than or equal to 121, limit 21.

URL: http://www.infoworld.com/article/2616674/database-administration/10-essential-performance-tips-for-mysql.html?page=2

My Question is: Do this really help in pagination queries?

Community
  • 1
  • 1
mwafi
  • 3,946
  • 8
  • 56
  • 83
  • `[LIMIT {[offset,] row_count | row_count OFFSET offset}]` ... so what is the implication, that you do the same thing except subtract one from `offset` ? – Drew Sep 26 '16 at 21:58
  • `limit with offset` vs `Greater than specific id with limit` ? – mwafi Sep 26 '16 at 22:00
  • 1
    Depends on your sort (presentation) and indexes. Which the indexes would be the same anyway under this theory --> you presentation is prob not always useful by `id` but rather some other strategy. Like by some date, a join, alpha. Depends what you are doing. – Drew Sep 26 '16 at 22:04
  • @mwafi: I personally think it's pretty naive to assume that all identifiers would always be a continuous sequence. – jakub wrona Sep 26 '16 at 22:17
  • 1
    @jakubwrona The excerpt above seems fine if the page size is 21. But sure, there are id gaps all over the place, but for page sizing that is irrelevant as I see it – Drew Sep 26 '16 at 23:27
  • [_My discussion of the problem_](https://mariadb.com/kb/en/pagination-optimization/), plus a workaround. – Rick James Sep 27 '16 at 18:31
  • The technique works _if_ you don't have to scan the entire table, but instead can use an index to "remember where you left off". – Rick James Sep 27 '16 at 18:37

0 Answers0