skip to Main Content

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.

2

Answers


  1. 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

    Login or Signup to reply.
  2. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search