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

Question

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'

Deference

So amazing!!! Thanks for the question, dont understand why it only got 23 upvotes. So fundamental question. – Muleteer

This question title is better because it came up first: "How to check if limit was used in mysql?" https://mcmap.net/q/212975/-how-to-check-if-limit-was-used-in-mysql-duplicate/1066234 (duplicate) – Gwenni

Answer 1

Take a look at SQL_CALC_FOUND_ROWS

Diastole

This was deprecated as of MySQL 8.0.17 – Bushweller

And the proposed solution by MySQL: Simply use two queries, one with LIMIT one without, see dev.mysql.com/doc/refman/8.0/en/… – Gwenni

I looked at it the suggested post-MySQL 8 solution, and it sounds like a generally bad idea. I guess it would be alright if your main reason for using LIMIT was to page a result set that would be too large to show/keep in memory otherwise, and the query is otherwise very fast. In all other cases - such as improving slow queries - we are left with the solution of streaming the entire result set to the client and applying the limit there. – Pevzner

Answer 2

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.

Coblenz

This seems like a good option to go without the SQL_CALC_FOUND_ROWS – Episternum

Answer 3

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.

Fabron

Sorry, I forgot to add that my SQL query uses WHERE as well to select all rows with a particular 'category_id' – Deference

nyx, wouldn't SELECT COUNT(*) FROM table_name WHERE condition still work? – Houseraising

"SELECT operations are usually cheap" - Not if you have a couple of millions of DB entries and no primary and no index. Be careful. – Gwenni

@Gwenni in that case you should not have done the first query at all. That would also affect that one. – Magnifico

Answer 4

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.

Porush

I like this specifically because it hits the db once, but was wondering if it would be more "expensive" than 2 SELECTs one being a simple COUNT(*) since it performs a JOIN. – Acred

This was deprecated as of MySQL 8.0.17 – Bushweller

Answer 5

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

Smoodge

Answer 6

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

Bestrew

Answer 7

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

Gives you the number of rows for your specific category.

Intimacy

SELECT COUNT(*) is more performant because it won't need to check for NULL value exclusion – Sandal