Sanchan World Blog

Display total rows count with LIMIT clause in MySQL

Assume there are lots of records in result set and want to display only few records. This concept is known as the pagination.

The sql query to achieve above is, as follows,

select * from books where bookcost > 3oo order by bookcost limit 0, 25;

The above query will check the condition and returns only 25 records out of hundreds of records. Now if we want to display the pagination which also tells the how many records are there.

Ex: showing record 1 to 25 of 1500

For this we need to use the count(*) option initially and run the query, then we will get the total results. There is one option which also provides the total number of records.

Use SQL_CALC_FOUND_ROWS and FOUND_ROWS() as a second query to find the total rows.

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name  WHERE id > 50 LIMIT 25;
mysql> SELECT FOUND_ROWS();

Here first query outputs the first 25 rows and also calculates the total number of records using the SQL_CALC_FOUND_ROWS option. Then second query actually outputs the total number of rows.