Problem Statement:-

There are many situation when we want to restrict the records that a query returns, but we want the total records that query had matched.Like, If we use LIMIT clause in the query then FOUND_ROWS() will not return the total records that query had found but the records less than or equal to the LIMIT parameter. But we can be interesting in the total records.

For example:-

There is a table customers that have 500 records and if we fire the following query

SELECT * from customers LIMIT 15

then

SELECT FOUND_ROWS()

will return 15. But we want 500 as the result of FOUND_ROWS().

Solution:-

To solve this, there is a clause that returns the total records instead of considering LIMIT clause.

SQL_CALC_FOUND_ROWS is used to get the total records.

For above problem, we can change the query like

SELECT SQL_CALC_FOUND_ROWS * from customers LIMIT 15

now if we fire

SELECT FOUND_ROWS()

then it will return 500.

Categories: MySQL