title: What is the most efficient way to paginate my site when querying with SQL?
body: <p>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.</p>
  
  <p>The code I'm using works something like this (page numbering starts at 0)</p>
  
  <p><a href="http://example.com/table?page=0" rel="nofollow noreferrer">http://example.com/table?page=0</a></p>
  
  <pre><code>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 &gt; 0) show a previous link
  if(count(resultset) &gt; per) show a next link
  
  unset(resultset[per])
  
  display results
  </code></pre>
  
  <p>Are there more efficient ways to do pagination than this?</p>
  
  <p>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.</p>
  
  <p>Is it more efficient to issue a <code>COUNT(*)</code> query to learn how many rows exist, then stream the results to the browser?</p>
  
  <p>Is this one of those "it depends on the size of your table, and whether the <code>count(*)</code> query requires a full table scan in the database backend", "do some profiling yourself" kind of questions?</p>