Is it possible to obtain both the count and results of a query in a single MySQL query?

I'm currently working on optimizing a MySQL query, and I'm wondering if there's a way to retrieve both the count of the results and the actual result set in a single query. I'm aware that I can achieve this using separate queries, one for the count and one for fetching the results, but I'm curious if there's a more efficient way to accomplish this within a single query.

Yes, you can use a subquery in SELECT :

Select *, (SELECT COUNT(id) FROM city) as total FROM city;

And here is the result :

enter image description here

or in join case you can use this method :

SELECT COUNT(*),id,type_id FROM 
   ( 
         SELECT proprety.id,type.id as type_id
         FROM 
            proprety LEFT JOIN type
         ON proprety.type_id=type.id 

    ) as t1;

and here is the result :

enter image description here

Do it in the other order.

Rather than count the QuerySet size, then fetch it, you should instead fetch it and then determine length.

You can convert a QuerySet to a list. This fetches the query.

queryset_list = list(queryset)

Then len() on that list can be found without a query to the database.

Back to Top