Substr() with Group by clause I've been working on solving the problem shown here, and a question arose

Here's the schema I created, my statement of the problem, and my test data:

Schema: Students

CREATE TABLE Students (group_id text, sql_quotient float);
INSERT INTO Students(group_id, sql_quotient)
VALUES 
    ( 'A', 25 ),
    ( 'B', 30 ),
    ( 'C', 40 ),
    ( 'A', 35 ),
    ( 'B', 20 );


Task: Display max average sql_quotient among all the groups.


group_id is guaranteed to be a Single character of range from A-Z.
Here for group A, avg is 30; for B, avg is 25; and for C, avg is 40;
hence, 40 should be displayed.


I tried the following 2 queries; both give me the right answer.

Query 1

select max(round(b.avg_quotient,2)) as answer 
         from 
          (SELECT AVG(sql_quotient) as avg_quotient FROM Students GROUP BY group_id) as b;


Runtime = 0.002378 sec

Query 2

select max(round(b.avg_quotient,2)) as answer 
     from 
      (SELECT AVG(sql_quotient) as avg_quotient FROM Students GROUP BY substr(group_id,1,1) )as b;  


Runtime = 0.000459 sec

The difference - the first query groups the data by group_id; the second by `substr(group_id,1,1).

As the second query applies an additional function, I would expect it to take longer. However, as you can see above, runtime of query no.2 is remarkably less than query no. 1.

My question: why does query 2 have a lower runtime than query 1, even though query 2 has one extra function (substr()).

Notes:


The schema is already defined. I don't know why the datatype of Id is text instead of char(1), and for the purposes of my question it's irrelevant.
I'm looking for the reason for the difference of runtime between these 2 queries, not the another query for same problem.
The Students table is created anew for each run, so this isn't a case of the data having to be read from disk on the first run, and being in memory on the second. To prove this, I ran the queries four more times, running Query 2, then Query 1, then 2 again, then 1 again. The run times were: 


Q2, 1st: 0.000493
Q1, 1st: 0.002779
Q2, 2nd: 0.000499
Q1, 2nd: 0.002787
No substring needed. No need to calculate the max()

Something like this,

SELECT id, avg(price)
FROM Students
GROUP BY id
ORDER BY avg(price) DESC
LIMIT 1;

+------+------------+
| id   | avg(price) |
+------+------------+
| C    |    40.0000 |
+------+------------+


That's likely simpler than having to calculate the max.

SELECT id, avg(price)
FROM Students
GROUP BY id
HAVING avg(price) = (
    SELECT max(avg)
    FROM (
        SELECT id, avg(price) AS avg
        FROM Students
        GROUP BY id
    ) AS t
);


timings

+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                                                           |
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
|        1 | 0.00008716 | SELECT id, avg(price) FROM Students GROUP BY id HAVING avg(price) = ( SELECT max(avg) FROM ( SELECT id, avg(price) AS avg FROM Students GROUP BY id) AS t ) |
|        2 | 0.00008053 | SELECT id, avg(price) FROM Students GROUP BY id ORDER BY avg(price) DESC LIMIT 1                                                                      |
|        3 | 0.00011303 | SELECT id, avg(price) FROM Students GROUP BY id HAVING avg(price) = ( SELECT max(avg) FROM ( SELECT id, avg(price) AS avg FROM Students GROUP BY id) AS t ) |
|        4 | 0.00006121 | SELECT id, avg(price) FROM Students GROUP BY id ORDER BY avg(price) DESC LIMIT 1                                                                      |
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------+