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) 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 `fields` 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?

Idyllic

Although you wouldn't have COUNT(*) in query2 – Whoa

Answer 1

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.

Johnathan

It's not quite race-condition proof, however, unless you do the two queries within a transaction. This generally isn't a problem, though. – Bobinette

By "reliable" I meant the SQL itself is always going to return the result you want, and by "bullet-proof" I meant that there are no MySQL bugs hampering what SQL you can use. Unlike using SQL_CALC_FOUND_ROWS with ORDER BY and LIMIT, according to the bug I mentioned. – Johnathan

On complex queries, using SQL_CALC_FOUND_ROWS to fetch the count in the same query will almost always be slower than doing two separate queries. This is because it means all rows will need to be retrieved in full, regardless of the limit, then only those specified in the LIMIT clause are returned. See also my response which has links. – Thundering

Depending on the reason you need this, you may also want to think of just not retrieving the total results. It's becoming a more common practice to implement auto-paging methods. Sites like Facebook, Twitter, Bing, and Google have been using this method for ages. – Nullity

Answer 2

I almost never do two queries.

Simply return one more row than is needed, only display 10 on the page, and if there are more than are displayed, display a "Next" button.

SELECT x, y, z FROM `table` WHERE `some_condition` LIMIT 0, 11

// Iterate through and display 10 rows.
// if there were 11 rows, display a "Next" button.

Your query should return in the order of most relevant first, chances are most people aren't going to care about going to page 236 out of 412.

When you do a google search and your results aren't on the first page, you likely go to page two, not nine.

Kimble

Actually, if I don't find it on the first page of a Google query, usually I do skip to page nine. – Delighted

@Delighted I heard this before but why do that? – Conquistador

A little late, but here is my reasoning. Some searches are dominated by search engine optimized link farms. So the first few pages are the different farms fighting it out for position number 1, the useful result is likely still associated with the query, just not on the top. – Delighted

COUNT is an aggregate function. How do you return the count and all the results in one query? The above query will only return 1 row, no matter what the LIMIT is set at. If you add GROUP BY, it'll return all results but the COUNT will be inaccurate – Borchers

The result of count is usefull to calculate how many pages will be needed to see the total number of rows. – Hacker

@Borchers count(*) was a mistake, obviously... I updated the query to include x,y,z columns. – Kimble

I'm way late to the question and answer. This will give you a "next button", but how do you even know if there's going to BE a page 9 unless you query first? If all you're looking for is a next and back, I like this approach! – Smashandgrab

Basically a very good idea, however if you want that your pages tells you how many pages you got for example Page: 3 / 12 then this will not help out here. Also displaying a last-page button is not possible this way. – Cloudburst

This is one of the approaches recommended by Percona: percona.com/blog/2008/09/24/… – Tip

This design only works for searching-like scnerios. In many cases the total number is still needed. – Ileac

Answer 3

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.

Thundering

@Thundering I had the same approach, however discovered a flaw with it today. The final page of results will not then have the pagination data. i.e., let's say each page should have 25 results, the last page will likely not have that many, let's say it has 7... that means the count(*) will never be run, and so no pagination will be displayed to the user. – Fao

No - if you are say, 200 results in, you query the next 25 and you only get 7 back, that tells you that the total number of results is 207 and therefore you don't need to do another query with COUNT(*) because you already know what it's going to say. You have all the information you need to show pagination. If you are having a problem with pagination not showing to the user then you have a bug somewhere else. – Thundering

Answer 4

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.

Thundering

Answer 5

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.

Helicline

Answer 6

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.

Butterfield

This query just returns the total number of record in the table; not the number of records that match the condition. – Ogee

The total number of records is what is needed for pagination (@Lawrence). – Carolecarolee

Oh, well, just add the where clause to the inner query and you get the right "total" alongside with the paged results (page is selected with the limit clause – Cutch

the sub-query count(*) would require the same where clause or else it won't return the correct number of results – Dowery

Answer 7

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

Correction

Link's dead, I guess this is the correct one: percona.com/files/presentations/ppc2009/…. Won't edit because not sure if it is. – Unrestraint

Answer 8

You can reuse most of the query in a subquery and set it to an identifier. For example a movie query that finds movies containing the letter 's' ordering by runtime would look like this on my site.

SELECT Movie.*, (
    SELECT Count(1) FROM Movie
        INNER JOIN MovieGenre 
        ON MovieGenre.MovieId = Movie.Id AND MovieGenre.GenreId = 11
    WHERE Title LIKE '%s%'
) AS Count FROM Movie 
    INNER JOIN MovieGenre 
    ON MovieGenre.MovieId = Movie.Id AND MovieGenre.GenreId = 11
WHERE Title LIKE '%s%' LIMIT 8;

Do note that I'm not a database expert, and am hoping someone will be able to optimize that a bit better. As it stands running it straight from the SQL command line interface they both take ~0.02 seconds on my laptop.

Antarctic

Answer 9

Best answer is to get all in one query and just remove total count from dataset later in application.

SELECT 'fields', (SELECT COUNT(*) FROM 'table') as 'total_count'
FROM 'table' WHERE 'some_condition' LIMIT 0, 10

Since you are doing pagination, you are retrieving 10, 15, 25, 50 rows, not whole dataset, so to remove column 'total_count' is very simple and fast. This way you can do it all in one connection.

I was wondering how it will work, so I run EXPLAIN ANALYZE on some of my queries and here in picture below, you can see that database is smart enough to recognize that it should only run count(*) once.

Bosky

It is still double-querying. – Froggy

@Froggy How? This is done in one query. And in one connection. I don't understand. Just because it has 2 selects does not mean it is double-querying. What you think double-querying means? – Bosky

Because you have to count each time when you change your offset and limit, because it's a paged request – Froggy

@Froggy what? and? still not double-querying. You should reread original question. – Bosky

Answer 10

SELECT * 
FROM table 
WHERE some_condition 
ORDER BY RAND()
LIMIT 0, 10

Sherris

This doesn't answer the question, and an order by rand is a really bad idea. – Maidamaidan