I am trying to paginate the results of an SQL query for use on a web page. The language and the database backend are PHP and SQLite.
The code I'm using works something like this (page numbering starts at 0)
http://example.com/table?page=0
page = request(page)
per = 10 // results per page
offset = page * per
// take one extra record so we know if a next link is needed
resultset = query(select columns from table where conditions limit offset, per + 1)
if(page > 0) show a previous link
if(count(resultset) > per) show a next link
unset(resultset[per])
display results
Are there more efficient ways to do pagination than this?
One problem that I can see with my current method is that I must store all 10 (or however many) results in memory before I start displaying them. I do this because PDO does not guarantee that the row count will be available.
Is it more efficient to issue a COUNT(*) query to learn how many rows exist, then stream the results to the browser?
Is this one of those "it depends on the size of your table, and whether the count(*) query requires a full table scan in the database backend", "do some profiling yourself" kind of questions?
