Find total number of results in mySQL query with offset+limit

Solution 1:

Take a look at SQL_CALC_FOUND_ROWS

Solution 2:

SELECT COUNT(*) FROM table_name WHERE column = 'value' will return the total number of records in a table matching that condition very quickly.

Database SELECT operations are usually "cheap" (resource-wise), so don't feel too bad about using them in a reasonable manner.

EDIT: Added WHERE after the OP mentioned that they need that feature.

Solution 3:

Considering that SQL_CALC_FOUND_ROWS requires invoking FOUND_ROWS() afterwards, if you wanted to fetch the total count with the results returned from your limit without having to invoke a second SELECT, I would use JOIN results derived from a subquery:

SELECT * FROM `table` JOIN (SELECT COUNT(*) FROM `table` WHERE `category_id` = 9) t2 WHERE `category_id` = 9 LIMIT 50

Note: Every derived table must have its own alias, so be sure to name the joined table. In my example I used t2.

Solution 4:

The SQL_CALC_FOUND_ROWS query modifier and accompanying FOUND_ROWS() function are deprecated as of MySQL 8.0.17 and will be removed in a future MySQL version. As a replacement, considering executing your query with LIMIT, and then a second query with COUNT(*) and without LIMIT to determine whether there are additional rows. For example, instead of these queries:

SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id > 100 LIMIT 10;
SELECT FOUND_ROWS();

Use these queries instead:

SELECT * FROM tbl_name WHERE id > 100 LIMIT 10;
SELECT COUNT(*) WHERE id > 100;

COUNT(*) is subject to certain optimizations. SQL_CALC_FOUND_ROWS causes some optimizations to be disabled.