Find total number of results in mySQL query with offset+limit
PhpMysqlCodeigniterPaginationPhp Problem Overview
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 depending 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'
Php Solutions
Solution 1 - Php
Take a look at SQL_CALC_FOUND_ROWS
Solution 2 - Php
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 - Php
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 - Php
> 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.
Solution 5 - Php
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
Solution 6 - Php
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
Solution 7 - Php
SELECT COUNT(id) FROM `table` WHERE `category_id` = 9
Gives you the number of rows for your specific category.