7 Answers
7

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 that affects ORDER BY queries making it much slower on large tables than the naive approach of two queries.

It's not quite race-condition proof, however, unless you do the two queries within a transaction. This generally isn't a problem, though.
–
NickZoicMay 4 '09 at 3:23

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.
–
staticsanMay 4 '09 at 4:30

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.
–
thomasrutterSep 8 '11 at 5:21

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
–
pixelfreakNov 29 '12 at 9:57

1

The result of count is usefull to calculate how many pages will be needed to see the total number of rows.
–
rvazquezglezMar 22 '13 at 18:21

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.

@thomasrutter 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.
–
duellsyAug 21 '12 at 6:34

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.
–
thomasrutterAug 22 '12 at 2:38

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.