Calculating COUNT(*) of a MAX() + GROUP BY query

Question

I have a SELECT query I'm executing which has a MAX() column and a GROUP BY clause, and in addition to the results of this query that I need to return to the client, I also need to return the total count of all results as well.

Essentially my query is this:

SELECT id, col1, col2, MAX(col3) as col3
FROM tbl
GROUP BY col1, col2

It'll usually have a WHERE clause too.

When returning this data to the client, I also specify LIMIT and OFFSET clauses to limit the number of results being retrieved and displayed on the UI at one time. For a good UX, I also return and display the total number of results that the above SELECT query would produce if it didn't have the LIMIT and OFFSET clauses, so that the UI can display e.g. "41-60 of 6500 results".

Currently, I use a WITH temporary table to get what I want:

WITH temp AS (
    SELECT id, col1, col2, MAX(col3) as col3
    FROM tbl
    GROUP BY col1, col2
)
SELECT COUNT(*) FROM temp

But I'm concerned about the efficiency of this. The sans-LIMIT-and-OFFSET query could return hundreds of thousands of rows, so I'm thinking that the WITH approach to getting the total count isn't the best way to do this.

Is there a more efficient way, which I'm not thinking of? Either a more efficient way to get the COUNT I need or some thoughts on reusing a previous COUNT value?

---

Example data

Assume this is the data in my table:

id   col1  col2  col3
______________________
1     5     8     30
2     5     8     33
3     5     9     40
4     6     8     30
5     6     8     31
6     6     8     32
7     6     9     39
8     7     8     33
9     7     8     32
10    8     8     34

So my SELECT query would return this (assume the client specified LIMIT 4 OFFSET 0):

SELECT id, col1, col2, max(col3) as col3
FROM tbl
GROUP BY col1, col2
LIMIT 4
OFFSET 0;
    id   col1  col2  col3
    ______________________
    2     5     8     33
    3     5     9     40
    6     6     8     32
    7     6     9     39

And then I'd use that query without the LIMIT and OFFSET clauses as a subquery and SELECT COUNT(*) from it, which would return 6, and I'd return both the 6 and the results to the client. The client runs this query automatically every 10 or so seconds to keep the UI refreshed, and every time the client presses the arrows at the bottom of the table on the UI to go to the next page (i.e. specify a new OFFSET).

---

The exact schema of this stripped-down case is this:

CREATE TABLE test (id INT NOT NULL, col1 TEXT, col2 TEXT, col3 INT, PRIMARY KEY (id));

The only difference between my schema and this is that I also join on another table based on the id, and there are more than just 2 columns in the GROUP BY clause. I kept this example minimal because the WITH part is what's really important here.

---

EXPLAIN reports that it's indeed creating a temporary table of all 10 rows, hence my concern:

> EXPLAIN WITH temp AS ( SELECT id, col1, col2, MAX(col3) AS col3 FROM test GROUP BY col1, col2 ) SELECT COUNT(*) FROM temp;                                                                            
+------+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
| id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+------+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
|    1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |   10 |                                 |
|    2 | DERIVED     | test       | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using temporary; Using filesort |
+------+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+

Answer 1

To determine the number of distinct groups a distinct query over the grouping columns is sufficient. No temp table is needed.

SELECT COUNT(DISTINCT col1, col2) FROM tbl

Depending on the data type of your grouping columns it might be beneficial to define a two-column index on (col1, col2).

Have a look at MySQL's GROUP BY Optimization documentation to see how this works.