Suppose, a web page displays the paginated result containing links to the pages that show other sections of a search result. There are MySQL language specific constructs that 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. In the pagination scenario, we can easily determine how many other pages are needed for the rest of the result.

Technically, a MySQL SELECT statement may include a LIMIT clause to restrict the number of rows the server returns to the client. In the cases where it is desirable to know how many rows the statement would have returned without the LIMIT, but without running the statement again, include a SQL_CALC_FOUND_ROWS option in the SELECT statement, and then invoke FOUND_ROWS() afterward:

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name

 -> WHERE id > 100 LIMIT 10;

mysql> SELECT FOUND_ROWS();

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.

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.

To embrace this strategy which mitigates the extravagance of a second COUNT query, another option is to be considered – SQL_NO_CACHE

MySQL Query caching is like a key-value cache with the key being the query itself and the resultset being the value. Once we turn on the cache, the pagination is faster with the second query using COUNT()!

When using SQL_CALC_FOUND_ROWS the application has to calculate the found rows every single time we request the page, because the query changes, while the COUNT() – Query always remains the same, meaning that its result comes from the query cache from the second time on.

So if the query cache is on, it is better to use COUNT() to leverage the performance gain provided by the query caching mechanism. It would be better to use FOUND_ROWS()in all other cases.

By DB Team, Software Associates