SQL: counting the number of matching results when using a LIMIT query

Question

Say the table usersin my MySQL database contains a very large number of records.

I need to go through all the users, but I want to do this only chunks at a time (i.e. using LIMITand OFFSET):

SELECT * FROM users LIMIT 100 OFFSET 200

Is it possible to find out the total number of users matched in the request, but only return their number?

In other words, is it possible for me to know in advance the total number of users without creating a separate request?

Answer 1

You can do this in (almost) one query using and : SQL_CALC_FOUND_ROWSFOUND_ROWS()

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

, , .

Answer 2

. : , , - .

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

Answer 3

In the SQL standard, this is not possible. I don’t know mysqlmuch, but I would suggest that this is not possible even in any SQL extension.