MySQL pagination without double-querying?

Question

I was wondering if there was a way to get the number of results from a MySQL query, and at the same time limit the results.

The way pagination works (as I understand it), first I do something like

query = SELECT COUNT(*) FROM `table` WHERE `some_condition`

After I get the num_rows(query), I have the number of results. But then to actually limit my results, I have to do a second query like:

query2 = SELECT COUNT(*) FROM `table` WHERE `some_condition` LIMIT 0, 10

My question: Is there anyway to both retrieve the total number of results that would be given, AND limit the results returned in a single query? Or any more efficient way of doing this. Thanks!

Answer 1

Another approach to avoiding double-querying is to fetch all the rows for the current page using a LIMIT clause first, then only do a second COUNT(*) query if the maximum number of rows were retrieved.

In many applications, the most likely outcome will be that all of the results fit on one page, and having to do pagination is the exception rather than the norm. In these cases, the first query will not retrieve the maximum number of results.

For example, answers on a stackoverflow question rarely spill onto a second page. Comments on an answer rarely spill over the limit of 5 or so required to show them all.

So in these applications you can simply just do a query with a LIMIT first, and then as long as that limit is not reached, you know exactly how many rows there are without the need to do a second COUNT(*) query - which should cover the majority of situations.

Answer 2

My answer may be late, but you can skip the second query (with the limit) and just filter the info through your back end script. In PHP for instance, you could do something like:

if($queryResult > 0) {
   $counter = 0;
   foreach($queryResult AS $result) {
       if($counter >= $startAt AND $counter < $numOfRows) {
            //do what you want here
       }
   $counter++;
   }
}

But of course, when you have thousands of records to consider, it becomes inefficient very fast. Pre-calculated count maybe a good idea to look into.

Here's a good read on the subject: http://www.percona.com/ppc2009/PPC2009_mysql_pagination.pdf