[Solved] Getting Corresponding Row Values to a GROUP BY with MIN

EverSQL Database Performance Knowledge Base

Getting Corresponding Row Values to a GROUP BY with MIN

I've got data that's something like this:

+------------+---------+-------+
|    Name    |  Time   | Flag  |
+------------+---------+-------+
| Bob        | 401     | 1     |
| Bob        | 204     | 0     |
| Dan        | 402     | 1     |
| Dan        | 210     | 0     |
| Jeff       | 204     | 0     |
| Fred       | 407     | 1     |
| Mike       | 415     | 1     |
| Mike       | 238     | 0     |
+------------+---------+-------+

I want to get each person's best time, but if the "flag" is set, their time should be divided by 2. For example, Bob's best time would be 200.5

Now I can do a relatively simple query to get this data like this:

SELECT userid,
       MIN(CASE WHEN flag = 1 THEN time / 2 ELSE time END) AS convertedTime,
       time,
       flag
FROM   times t
GROUP  BY userid
ORDER  BY convertedtime ASC

The problem here is that is doesn't return the proper corresponding data for the time and flag, getting this data:

Bob     200.5   204     0

instead of the correct data

Bob     200.5   401     1

Of course I see the issue with the previous query and I've fixed it with this:

 SELECT userid,
       MIN(convertedtime) AS convertedTime,
       (SELECT time
        FROM   times
        WHERE  MIN(convertedtime) = CASE
                                      WHEN flag = 1 THEN time / 2
                                      ELSE time
                                    end
        LIMIT  1)         AS time,
       (SELECT flag
        FROM   times
        WHERE  MIN(convertedtime) = CASE
                                      WHEN flag = 1 THEN time / 2
                                      ELSE time
                                    end
        LIMIT  1)         AS flag
FROM   (SELECT userid,
               CASE
                 WHEN flag = 1 THEN time / 2
                 ELSE time
               end AS convertedTime,
               time,
               flag
        FROM   times t) AS t
GROUP  BY userid
ORDER  BY convertedtime ASC

SQLFiddle

This does work, but I feel like there has to be a better and more efficient way of doing this. In my actual query, the part where I'm dividing the time by 2 is a much longer formula and I've got thousands of rows so it's very slow.

So the question is, is there a better/more efficient query for this?

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `times` ADD INDEX `times_idx_userid` (`userid`);
The optimized query:
SELECT
        t.userid,
        MIN(CASE 
            WHEN t.flag = 1 THEN t.time / 2 
            ELSE t.time END) AS convertedTime,
t.time,
t.flag 
FROM
times t 
GROUP BY
t.userid 
ORDER BY
convertedtime ASC

Related Articles



* original question posted on StackOverflow here.