1
\$\begingroup\$

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 |
+------+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
\$\endgroup\$
  • \$\begingroup\$ What is the real-world problem your code addresses? Please fill in the context of why we're doing this, and the question will be much improved. \$\endgroup\$ – Toby Speight Sep 13 '18 at 20:41
  • \$\begingroup\$ What about SELECT COUNT(DISTINCT col1, col2) FROM tbl?. \$\endgroup\$ – aventurin Sep 13 '18 at 22:29
  • \$\begingroup\$ @TobySpeight I don't think the description of the actual problem would help here. The example data I gave is very representative of the actual data that will reside in my database, with the exception that the real table will have 100k+ rows in it. Plus, this is a company problem I've solved, so the actual description of the problem and any actual data are proprietary, and sharing it here would likely get me fired. I know the "on topic" Help page asks for non-hypothetical data/context, but this presents a problem for people asking work questions like this. Any recommendations on that front? \$\endgroup\$ – villapx Sep 17 '18 at 15:52
  • \$\begingroup\$ I thought there was a question on Code Review Meta about the best way to proceed with code you can't fully share, but I was unable to find it. It may be a good idea to ask there (you might get pointed at an existing Meta question, or you might get freshly-typed advice; either will be helpful). \$\endgroup\$ – Toby Speight Sep 17 '18 at 16:16
  • \$\begingroup\$ @aventurin nice, that is a performance improvement; it doesn't have the <derived2> table in the EXPLAIN output anymore \$\endgroup\$ – villapx Sep 17 '18 at 18:19
3
\$\begingroup\$

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.

\$\endgroup\$
  • 1
    \$\begingroup\$ Definitely more efficient than my current query, because it doesn't create a temporary table. Note that I previously asked this question on StackOverflow (asked it here too because the SO post wasn't getting any attention, and because I determined CR.SE was more suited for the "more efficient plz" type of question), and eventually I got a response there that works as well. \$\endgroup\$ – villapx Sep 18 '18 at 16:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.