In MariaDB, you can use the LIMIT clause to reduce the number of rows returned by a query. The FOUND_ROWS() function can be used in such queries to return the number of rows that would have been returned, had the LIMIT clause not been included.
This can be handy, because it allows you to get this information without having to run the query again.
The FOUND_ROWS() function can also be used to return the number of rows that were actually returned by the limited statement, if that’s what you need.
Syntax
The syntax goes like this:
FOUND_ROWS()No arguments are required or accepted.
To get the number of rows returned by a statement, include SQL_CALC_FOUND_ROWS in the statement, and then run FOUND_ROWS() in a separate query (after you’ve run the initial query).
Example
Here’s an example to demonstrate how the function works.
Suppose we create a table like this:
SELECT SQL_CALC_FOUND_ROWS *
FROM Pets
LIMIT 2;Result:
+-------+-----------+---------+---------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | +-------+-----------+---------+---------+------------+ | 1 | 2 | 3 | Fluffy | 2020-11-20 | | 2 | 3 | 3 | Fetch | 2019-08-16 | +-------+-----------+---------+---------+------------+
Only two rows were returned, as specified by the LIMIT clause.
However, let’s now use FOUND_ROWS() to see how many rows would have been returned if we hadn’t used the LIMIT clause:
SELECT FOUND_ROWS();Result:
+--------------+ | FOUND_ROWS() | +--------------+ | 8 | +--------------+
It tells us that eight rows would have been returned if we hadn’t used the LIMIT clause.
We can verify this by running the query without the LIMIT clause:
SELECT *
FROM Pets;Result:
+-------+-----------+---------+---------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | +-------+-----------+---------+---------+------------+ | 1 | 2 | 3 | Fluffy | 2020-11-20 | | 2 | 3 | 3 | Fetch | 2019-08-16 | | 3 | 2 | 2 | Scratch | 2018-10-01 | | 4 | 3 | 3 | Wag | 2020-03-15 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 6 | 3 | 4 | Fluffy | 2020-09-17 | | 7 | 3 | 2 | Bark | NULL | | 8 | 2 | 4 | Meow | NULL | +-------+-----------+---------+---------+------------+
I also removed the SQL_CALC_FOUND_ROWS from the statement, as we didn’t need it this time around.
Omitting the SQL_CALC_FOUND_ROWS Option
If you omit the SQL_CALC_FOUND_ROWS option, the FOUND_ROWS() function returns the actual number of rows returned. In other words, it returns the number of rows returned after the LIMIT clause has been applied.
Here’s the same example without the SQL_CALC_FOUND_ROWS option:
SELECT *
FROM Pets
LIMIT 2;Result:
+-------+-----------+---------+---------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | +-------+-----------+---------+---------+------------+ | 1 | 2 | 3 | Fluffy | 2020-11-20 | | 2 | 3 | 3 | Fetch | 2019-08-16 | +-------+-----------+---------+---------+------------+
Now run FOUND_ROWS():
SELECT FOUND_ROWS();Result:
+--------------+ | FOUND_ROWS() | +--------------+ | 2 | +--------------+
This time FOUND_ROWS() returned 2 instead of 8.
Queries Without a LIMIT Clause
The same concept can be applied to queries without a LIMIT clause.
Example:
SELECT *
FROM Pets;Result:
+-------+-----------+---------+---------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | +-------+-----------+---------+---------+------------+ | 1 | 2 | 3 | Fluffy | 2020-11-20 | | 2 | 3 | 3 | Fetch | 2019-08-16 | | 3 | 2 | 2 | Scratch | 2018-10-01 | | 4 | 3 | 3 | Wag | 2020-03-15 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 6 | 3 | 4 | Fluffy | 2020-09-17 | | 7 | 3 | 2 | Bark | NULL | | 8 | 2 | 4 | Meow | NULL | +-------+-----------+---------+---------+------------+
Now run FOUND_ROWS():
SELECT FOUND_ROWS();Result:
+--------------+ | FOUND_ROWS() | +--------------+ | 8 | +--------------+
Other Statements
The FOUND_ROWS() function can also be used on some other statements, such as SHOW, DESCRIBE, and HELP.