SQL_CALC_FOUND_ROWS : Get total records even if limit clause in QUERY

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.