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

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 an 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.

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

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.

Answer 6

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