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'

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

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 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.

View more solutions

Comments

[Nyxynyx]

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

[Nick ODell]

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

[karmiphuc]

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

[Hasan Can Saral]

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.

[JT Turner]

This was deprecated as of MySQL 8.0.17

[JT Turner]

This was deprecated as of MySQL 8.0.17

[Avatar]

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

[Avatar]

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

[Guss]

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.

[Kopi Bryant]

This seems like a good option to go without the SQL_CALC_FOUND_ROWS