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

Question

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?

Answer 1

Unfortunately no. You need to do two queries : one to fetch the total number of users, the other to fetch a single page of users.

select count(*) from users;
select * from users limit 0,10;

Answer 2

It is not possible in SQL standard. I do not know mysql much, but I would assume it is not possible even in any SQL extension.

Answer 3

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.