• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Pagination best practices

 
Ranch Hand
Posts: 102
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ehy ranchers,
in my web application I need to display some tabular data about records retrieved from a MySQL DB. The number of records is circa 180.000, so not very huge but neither low, but I'm forecasting an increase to reach a final number of about 500.000-600.000 records.
Of course, pagination is needed.
As for now, what I do to handle the pagination is retrieve all the records, skip the first K records (where K = (current_page - 1) * records_per_page) and then display records_per_page records.
This solution seems quite dumb to me, although it has been very fast to program and it actually does correctly what it's meant for.
I also thought about the LIMIT clause directly in the SQL statement (I'm actually using MySQL as database server), but with this solution I can't know the number of total records and I'd be bound to execute 2 queries (the first to know how many records satisfy a certain condition, the second to actually retrieve a subset of those records).
I wonder if are there some common patterns to deal with pagination of a big number of records, a practical and elegant way to handle subsets of a set of data, knowing the dimension of the entire set and subset, without actually retrieving the entire set (which could result in some serious memory leek sooner or later).
Any ideas ?
 
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Two queries are usually needed: one to get the slice of records to display, and one to get the count(*) with the total number of records.
 
Matteo Di Furia
Ranch Hand
Posts: 102
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
So, the benefit gained by selecting just a small set of data overcome the need to execute two qweries. This of course, makes sense to me, but I renew my question : no patterns/best practices to deal with it, other than doing two queries everytime ?
I was also thinking about storing somewhere the dimension of the complete set, but this would become early hard to mantain, since the conditions applied to the retrieving query can be various and you'll need to store every total count for any combination. Too messy !
 
Bear Bibeault
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I know of no way to get around the double query, but I'm not a DB guru either. You might want to ask in the JDBC forum. I can move this there if you'd like.

With regards to the count, in the pagination sub-system that I set up, the dataset interface consists of not only the slice of data, but the metadata that accompanies it: for example, the total count, the page size, the page number, the sort column and the sort direction.
[ October 16, 2008: Message edited by: Bear Bibeault ]
 
Matteo Di Furia
Ranch Hand
Posts: 102
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Mmm, yes, I also thought about a pagination system dealing with metadata, and I'll probably resolve all this setting it up. I just wanted to be sure there's not a well-known pattern out there before reinventing the wheel (no need to spend time to upgrade a system with a solution which is not the optimal one and the benefits are not evident).
About moving this to the JDBC forum, I think this is not a problem strictly bound to database engines, you could imagine a situation in which a large amount of data is retrived from different sources (file system, web services or whatever) and must be paginated. It seems to me it's just a design problem, rather than a database issue.
[ October 16, 2008: Message edited by: Matteo Di Furia ]
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic