How the FOUND_ROWS() function works in Mariadb?

The MariaDB FOUND_ROWS() function is used to retrieve the number of rows that would be returned by a SELECT statement, regardless of any LIMIT clause that may have been applied. It is particularly useful in situations where you need to know the total number of rows that match a particular condition, even if you only want to display a subset of those rows.

Syntax

The syntax for the MariaDB FOUND_ROWS() function is as follows:

FOUND_ROWS()

This function takes no arguments and returns an integer value representing the number of rows that would be returned by the most recent SELECT statement that was executed.

Examples

Example 1: Retrieving the total number of rows

In this example, we’ll demonstrate how to use the FOUND_ROWS() function to retrieve the total number of rows in a table, even when using a LIMIT clause.

DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

INSERT INTO employees VALUES
    (1, 'John Doe', 32),
    (2, 'Jane Smith', 27),
    (3, 'Michael Johnson', 41),
    (4, 'Emily Davis', 29),
    (5, 'David Wilson', 35);

SELECT * FROM employees LIMIT 2;

The output of this statement will be:

+----+------------+------+
| id | name       | age  |
+----+------------+------+
|  1 | John Doe   |   32 |
|  2 | Jane Smith |   27 |
+----+------------+------+

To get the total number of rows in the table, we can use the FOUND_ROWS() function:

SELECT FOUND_ROWS();

The output of this statement will be:

+---------------+
| FOUND_ROWS()  |
+---------------+
|             5 |
+---------------+

Example 2: Retrieving the number of rows that match a condition

In this example, we’ll use the FOUND_ROWS() function to retrieve the number of rows that match a particular condition, even when using a LIMIT clause.

SELECT * FROM employees WHERE age > 30 LIMIT 2;

The output of this statement will be:

+----+-----------------+------+
| id | name            | age  |
+----+-----------------+------+
|  1 | John Doe        |   32 |
|  3 | Michael Johnson |   41 |
+----+-----------------+------+

To get the total number of rows that match the condition age > 30, we can use the FOUND_ROWS() function:

SELECT FOUND_ROWS();

The output of this statement will be:

+---------------+
| FOUND_ROWS()  |
+---------------+
|             3 |
+---------------+

Example 3: Using FOUND_ROWS() with a complex query

In this example, we’ll demonstrate how to use the FOUND_ROWS() function with a more complex query that involves joins and subqueries.

DROP TABLE IF EXISTS departments;
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO departments VALUES
    (1, 'Sales'),
    (2, 'Marketing'),
    (3, 'Engineering');

DROP TABLE IF EXISTS employee_department;
CREATE TABLE employee_department (
    employee_id INT,
    department_id INT,
    PRIMARY KEY (employee_id, department_id),
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

INSERT INTO employee_department VALUES
    (1, 1),
    (2, 2),
    (3, 3),
    (4, 1),
    (5, 3);

SELECT e.name, d.name
FROM employees e
JOIN employee_department ed ON e.id = ed.employee_id
JOIN departments d ON ed.department_id = d.id
WHERE d.name = 'Engineering'
LIMIT 1;

The output of this statement will be:

+-----------------+-------------+
| name            | name        |
+-----------------+-------------+
| Michael Johnson | Engineering |
+-----------------+-------------+

To get the total number of rows that match the condition d.name = 'Engineering', we can use the FOUND_ROWS() function:

SELECT FOUND_ROWS();

The output of this statement will be:

+---------------+
| FOUND_ROWS()  |
+---------------+
|             2 |
+---------------+

Example 4: Using FOUND_ROWS() with a subquery

In this example, we’ll demonstrate how to use the FOUND_ROWS() function with a subquery.

SELECT *
FROM employees
WHERE id IN (
    SELECT employee_id
    FROM employee_department
    WHERE department_id = 1
)
LIMIT 2;

The output of this statement will be:

+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | John Doe    |   32 |
|  4 | Emily Davis |   29 |
+----+-------------+------+

To get the total number of rows that match the subquery condition, we can use the FOUND_ROWS() function:

SELECT FOUND_ROWS();

The output of this statement will be:

+---------------+
| FOUND_ROWS()  |
+---------------+
|             2 |
+---------------+

Related Functions

Here are a few functions related to MariaDB FOUND_ROWS():

• MariaDB ROW_COUNT() function is used to retrieve the number of rows affected by the most recent statement that modified data (e.g., INSERT, UPDATE, or DELETE).
• MariaDB SQL_CALC_FOUND_ROWS is a query modifier that is used in conjunction with FOUND_ROWS() to retrieve the total number of rows that would be returned by a SELECT statement without the LIMIT clause.
• MariaDB SHOW WARNINGS is used to retrieve information about the most recent statement that generated warnings or errors.

Conclusion

The MariaDB FOUND_ROWS() function is a powerful tool for retrieving the total number of rows that match a particular condition, even when using a LIMIT clause or other filtering techniques. By understanding how to use this function effectively, you can write more efficient and robust queries, and gain deeper insights into your data.