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 ?
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 !
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 ]
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 ]