Paging Through Data 2.0
For a long time, whenever I wanted to do paging to browse through a table, I used to run 2 queries. The first would get the results, and the second would be an almost identical query, with a count() instead of fields, and I’d use the result of the second query to figure out how many pages I would need.
Now there’s a better solution, and it’s called found_rows(). Say you have a simple select statement:
select * from sometable LIMIT 10;
You don’t know how many rows are in the table, so you need to do a second query:
select count(1) from sometable;
Now you can calculate the number of pages.
Using found_rows, you write your first query as:
select SQL_CALC_FOUND_ROWS * from sometable LIMIT 10; select found_rows();
Pagination with MySQL couldn’t be easier.
While this won’t work every time, it’s a great way to page through simple data. Hope this helps someone!
The second SELECT returns a number indicating how many rows the first SELECT would have returned had it been written without the LIMIT clause.
In the absence of the SQL_CALC_FOUND_ROWS option in the most recent SELECT statement, FOUND_ROWS() returns the number of rows in the result set returned by that statement.
The row count available through FOUND_ROWS() is transient and not intended to be available past the statement following the SELECT SQL_CALC_FOUND_ROWS statement. If you need to refer to the value later, save it:
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM … ; mysql> SET @rows = FOUND_ROWS(); If you are using SELECT SQL_CALC_FOUND_ROWS, MySQL must calculate how many rows are in the full result set. However, this is faster than running the query again without LIMIT, because the result set need not be sent to the client.
SQL_CALC_FOUND_ROWS and FOUND_ROWS() can be useful in situations when you want to restrict the number of rows that a query returns, but also determine the number of rows in the full result set without running the query again. An example is a Web script that presents a paged display containing links to the pages that show other sections of a search result. Using FOUND_ROWS() allows you to determine how many other pages are needed for the rest of the result.
The use of SQL_CALC_FOUND_ROWS and FOUND_ROWS() is more complex for UNION statements than for simple SELECT statements, because LIMIT may occur at multiple places in a UNION. It may be applied to individual SELECT statements in the UNION, or global to the UNION result as a whole.
The intent of SQL_CALC_FOUND_ROWS for UNION is that it should return the row count that would be returned without a global LIMIT. The conditions for use of SQL_CALC_FOUND_ROWS with UNION are:
The SQL_CALC_FOUND_ROWS keyword must appear in the first SELECT of the UNION.
The value of FOUND_ROWS() is exact only if UNION ALL is used. If UNION without ALL is used, duplicate removal occurs and the value of FOUND_ROWS() is only approximate.
If no LIMIT is present in the UNION, SQL_CALC_FOUND_ROWS is ignored and returns the number of rows in the temporary table that is created to process the UNION.
Source: MySQL’s Docs - Information Functions
If you found this post helpful, please consider sharing to your network. I'm also available to help you be successful with your distributed systems! Please reach out if you're interested in working with me, and I'll be happy to schedule a free one-hour consultation.