MySQL pagination without double-querying?

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.

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.

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.