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

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 开发者_Python百科know in advance the total number of users there are, without making a separate query?

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.

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;

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.