Get total number of rows when using LIMIT

Get total number of rows when using LIMIT

Hope this article finds you well.

Today friends, we will talk about very common issue which most of the developer face many time.

That is, getting total number of rows while using limit clause. This is very basic problem, i think almost every developer faced this issue at least once.

So i am here with the solution and want to share with you my friends…

As we know, MySQL SELECT statement includes a LIMIT clause that can be used to restrict the result set that how much data will be returned from the database. This is great for pagination but in most of the cases you also want to get the total number of rows as well, which mostly requires a second query. With MySQL there is a special option that can be passed to SELECT statement to do this calculation for you automatically.

First we see what the documentation says about LIMIT Clause.

“The LIMIT clause is used in the SELECT statement to constrain the number of rows in a result set. The LIMIT clause accepts one or two arguments. The values of both arguments must be zero or positive integers.

The following illustrates the LIMIT clause syntax with two arguments:
SELECT column1, column2,… FROM table LIMIT offset , count;
With two arguments, the first argument “offset” specifies the offset of the first row to return, and the second argument “count” specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1)”

This means I can write a query to select limited records something like this (Bloggers is just a table in the sample MySql Database)…

SELECT * FROM Bloggers LIMIT 0, 50;

The above query will return the first 50 rows from the Bloggers table. To get the next 50, we would have to change the LIMIT clause to ‘LIMIT 50, 50’ and the next 50 would be ‘LIMIT 50, 50’, and so on.

Now one more thing, if you specify an offset that is larger than the total number of rows in your table, you simply get empty resultset(no error). i.e. no records from the database.
However if you building a user interface where you have to display the whole data via pagination and you need to know when to stop displaying “next” button/link without relying on the empty result set.

Then there are 2 options.

1 – Build another query to get number to records from the the database and on the basis of that count you can make decision when to stop displaying “next” link.

Selet count(*) as total from Bloggers.

But this is extra overhead and little bit slow method.

2 – Second and most efficient method is to use of “SQL_CALC_FOUND_ROWS” option in the query which is required for the FOUND_ROWS() function to work. So you are in fact still having to execute two queries, but you’re not having to run a COUNT() query on your data since that’s already done by the database for you.

So the SQL query changes to this…
SELECT SQL_CALC_FOUND_ROWS * FROM Bloggers LIMIT 0, 50;

Then immediately after running your query to fetch the data, you execute the following query:

SELECT FOUND_ROWS();

This is because SQL_CALC_FOUND_ROWS does not save the result.

The above query will return the total number of records which is the same as your original query would have returned if you didn’t have the LIMIT clause.
So go through the whole process again. below is the query.
Lets assume Blogger table has 165 records.

SELECT SQL_CALC_FOUND_ROWS * FROM Bloggers LIMIT 0, 50;       //will return 50 records

SELECT FOUND_ROWS(); // will return 165

SELECT SQL_CALC_FOUND_ROWS * FROM Bloggers LIMIT 50, 50;    //will return 50 records

SELECT FOUND_ROWS(); // will return 165
So as you can see that the LIMIT Clause does not have an effect on the count returned by FOUND_ROWS().

Hope this article will help you to code better.

This is Lalit signing off for today. I will catch you guys in the next one.

Leave a Reply