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

countMySQL

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:

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

Best Answer

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:

a good query
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.

Related Solutions

An Effective Way to Count the Number of Pages in Oracle

If the typical use pattern is to page through all the data, then all the records will eventually need to be sent to the client. In this scenario re-querying the database for every page is in-efficient. You should consider running the query once and paging through a cache on the client side.

If on the other hand the typical use pattern is to page through a very small amount of the data, then this method may be appropriate. Nick Chammas' comment on using ROW_NUMBER() is fleshed out by Tom Kyte in a Oracle Magazine article.

SELECT * FROM (
   SELECT /*+ first_rows(25) */
      your_columns,
      Row_Number() Over (Order By something unique) rn
   FROM your_tables 
   )
WHERE rn BETWEEN :n AND :m 
ORDER BY rn;

I recommend you read the whole article. Note in particular the warning to sort by something unique so that the results are consistent between pages (given static data):

You need to order by something unique for these pagination queries, so that ROW_NUMBER is assigned deterministically to the rows each and every time.

SQL – How to Efficiently Count Rows by Limiting Found Rows

First, it's almost never OK to use LIMIT without ORDER BY, because you will not get consistent results across different executions of the query. So, especially for the pagination queries, I'd use:

SELECT <list of columns>
FROM <tables>
WHERE <conditions>
ORDER BY <some columns>
LIMIT rows_per_page
OFFSET x ;

Now, to find the number of pages this query could return - and with the extra condition that you will not use more than 50 pages - I'd try a separate query;

SELECT CEIL( COUNT(*) / 50 ) AS pages
FROM
  ( SELECT NULL
    FROM <tables>
    WHERE <conditions>
    LIMIT 50 * rows_per_page
  ) AS x ;

(and that is an exception to the "never use LIMIT without ORDER BY" rule above :)