Is SELECT COUNT GROUP BY more efficient than counting a result set?

Question

I have a table with a rank column which can be either 1, 2 or 3. I need to count the number of records for each rank, but I also need to display all the data ordered by rank.

From my perspective, the two ways to accomplish this would be:

1. Pull out the data with a normal SELECT and then run a second query to obtain counts:

   SELECT rank, COUNT(id) FROM tablename GROUP BY rank
   

2. Pull out the data with a normal SELECT and then iterate twice over the result set I get: once to count the occurrences of each rank, and the second time to actually display the data.

Which way would be more performant? Does it depend on table size? I imagine for large tables the SELECT COUNT would be a tad slower than counting in (PHP, ASP.NET, Java), in particular if there's more than 3 discrete values I want to count.

Answer 1

The answer depends a great deal on how well organized your data is and the query itself.

For example, look at the query you have in the question:

SELECT rank, COUNT(id) FROM tablename GROUP BY rank 

The first thing I think about with this query is whether the table is properly indexed.

OBSERVATION #1

If tablename had no indexes, a full table scan would be required.

OBSERVATION #2

If tablename had an index on rank, you still get a full table scan because of the MySQL Query Optimizer ruling out the use of the index because of factors such as key distribution and the possibility of having to lookup each id for every rank during a full index scan.

OBSERVATION #3

If the table had a compound index of (rank,id), then you can a full index scan. In most cases, a full index scan that never references the table for non-indexed columns would be faster than a full index scan that does (See OBSERVATION #2)

OBSERVATION #4

If the query was written slightly different

SELECT rank, COUNT(1) FROM tablename GROUP BY rank 

then an index on just the rank column would suffice and produce a full index scan.

CONCLUSION

In light of these observtions, it is definitely a thing of beauty to present to the MySQL Query Optimizer two things:

1. a good query
2. proper indexes for all tables in the query

In retrospect, it is also good to give the MySQL Query Optimizer as much of an advantage upfront as possible.

Answer 2

The recommended approach to improve performance is to:

a) Use the database to do the heavy lifting for counting, computations (where possible)

b) Reduce the number of trips to the database and the amount of data brought back on each trip

In that light, I am not sure what you want to use the rank count for, but the query below will return the data ordered by rank, but a "repeated" column with the number of records for each rank is also included

SELECT t.rank, col1, col2, rankcount 
FROM tablename t 
  INNER JOIN 
     (SELECT rank, COUNT(id) as rankcount FROM tablename t2 GROUP BY rank) AS t3 
    ON t.rank = t3.rank  
ORDER BY t.rank