CODEWITHSUNDEEP
sqlmysqlcountlimit
jd.
jd.

Reputation: 4098

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

Upvotes: 3

Views: 1687

Answers (3)

Jørn Schou-Rode
Jørn Schou-Rode

Reputation: 38366

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.

Upvotes: 4

van
van

Reputation: 77072

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.

Upvotes: 0

Stephen Curran
Stephen Curran

Reputation: 7433

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;

Upvotes: 0

Related Questions