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

I'm doing a pagination feature using Codeigniter but I think this applies to PHP/mySQL coding in general.

I am retrieving directory listings using offset and limit dependin开发者_StackOverflow中文版g on how many results I want per page. However to know the total number of pages required, I need to know (total number of results)/(limit). Right now I am thinking of running the SQL query a second time then count the number of rows required but without using LIMIT. But I think this seems to be a waste of computational resources.

Are there any better ways? Thanks!

EDIT: My SQL query uses WHERE as well to select all rows with a particular 'category_id'

---

Take a look at SQL_CALC_FOUND_ROWS

---

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.

---

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.

---

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.

---

you can use 2 queries as below, One to fetch the data with limit and other to get the no of total matched rows.

Ex:

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

As described by Mysql guide , this is the most optimized way, and also SQL_CALC_FOUND_ROWS query modifier and FOUND_ROWS() function are deprecated as of MySQL 8.0.17

---

as of final of 2021, why not:

SELECT 
t1.*, 
COUNT(t1.*) OVER (PARTITION BY RowCounter) as TotalRecords
FROM (
    SELECT a, b, c, 1 as RowCounter 
    FROM MyTable
) t1 
LIMIT 120,10

using a subquery with a column marking every row with the same value, will give us the possibility to count all of the same values of the the resulted column with PARTITION BY window function's group

---

SELECT COUNT(id) FROM `table` WHERE `category_id` = 9

Gives you the number of rows for your specific category.