MySQL pagination without double-querying?

user_100208 0
Viewed 67509

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) is to first 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:

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

Is there any way to both retrieve the total number of results that would be given, AND limit the results returned in a single query? Or are there any other efficient ways of achieving this?

double pagination mysql
9 Answers

No, that's how many applications that want to paginate have to do it. It's reliable and bullet-proof, albeit it makes the query twice, but you can cache the count for a few seconds and that will help a lot.

The other way is to use SQL_CALC_FOUND_ROWS clause and then call SELECT FOUND_ROWS(). Apart from the fact you have to put the FOUND_ROWS() call afterwards, there is a problem with this: there is a bug in MySQL that this tickles which affects ORDER BY queries making it much slower on large tables than the naive approach of two queries.

staticsan_288320

In most situations it is much faster and less resource intensive to do it in two separate queries than to do it in one, even though that seems counter-intuitive.

If you use SQL_CALC_FOUND_ROWS, then for large tables it makes your query much slower, significantly slower even than executing two queries, the first with a COUNT(*) and the second with a LIMIT. The reason for this is that SQL_CALC_FOUND_ROWS causes the LIMIT clause to be applied after fetching the rows instead of before, so it fetches the entire row for all possible results before applying the limits. This can't be satisfied by an index because it actually fetches the data.

If you take the two queries approach, the first one only fetching COUNT(*) and not actually fetching and actual data, this can be satisfied much more quickly because it can usually use indexes and doesn't have to fetch the actual row data for every row it looks at. Then, the second query only needs to look at the first $offset + $limit rows and then return.

This post from the MySQL performance blog explains this further:

http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

For more information on optimising pagination, check this post and this post.

user_532120

For anyone looking for an answer in 2020. As per MySQL documentation:

The SQL_CALC_FOUND_ROWS query modifier and accompanying FOUND_ROWS() function are deprecated as of MySQL 8.0.17 and will be removed in a future MySQL version. As a replacement, considering executing your query with LIMIT, and then a second query with COUNT(*) and without LIMIT to determine whether there are additional rows.

I guess that settles that.

user_75506220 
SELECT col, col2, (SELECT COUNT(*) FROM `table`) / 10 AS total FROM `table` WHERE `some_condition` LIMIT 0, 10

Where 10 is the page size and 0 is the page number, you need to use pageNumber - 1 in the query.

user_988360

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

user_11076140
Related