What is the most efficient way to paginate my site when querying with SQL?

Question

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? JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

Answer 1

I've opted to go with the COUNT(*) two query method, because it allows me to create a link directly to the last page, which the other method does not allow. Performing the count first also allows me to stream the results, and so should work well with higher numbers of records with less memory. Consistency between pages is not an issue for me. Thank you for your help.