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 ?