SQL: Counting number of matching results when using a LIMIT query

count, limit, mysql, sql

Solution

You can do it in (almost) one query, using `SQL_CALC_FOUND_ROWS` and `FOUND_ROWS()`:

SELECT SQL_CALC_FOUND_ROWS * FROM users LIMIT 100 OFFSET 200;
SELECT FOUND_ROWS();

While you still end up with two result sets, the actual query is only executed once, which saves you from repetitive coding and possible some wasted CPU cycles.

Problem

Say the `users` table in my MySQL DB contains a very large number of entries. I need to go through all the users, but I want to do it only chunks at a time (i.e. using `LIMIT` and `OFFSET`): `SELECT * FROM users LIMIT 100 OFFSET 200` Is it possible to know the total number of users matched in the query, but only return a LIMIT number of them ? In other words, is it possible for me to know in advance the total number of users there are, without making a separate query?

Original source