How can I find the highest score each day in MySQL?

Question

We have a game where players run through a map, and are scored for it. We need to find out which attempts had the high score on each map, and set their isHighScore flag to true. Only one attempt can have the high score for each map - if two attempts have the same score, only the attempt which came first chronologically should have its isHighScore flag set.

The SQL we currently have looks like this:

UPDATE attempts AS A1
SET isHighScore = 1
WHERE A1.ID =
(
    SELECT A2.ID
    FROM (SELECT ID, score, mapID, `date` FROM attempts) AS A2
    WHERE A2.mapID = A1.mapID
    ORDER BY A2.score DESC, A2.date ASC
    LIMIT 1
)

(The (SELECT ... FROM attempts) subquery is due to this issue)

The above takes longer than the timeout to run on a table with around 75k entries (and yes, there is an index on mapID, score, date). I assumed it was because the innermost query copies the entire attempts table to a temp-table, but moving the WHERE mapID = A1.mapID conditional into that query gives a syntax error, so I'm not sure what to do about that. Also, the inner-query runs once for every row - maybe there's a way to fix that?

Does anyone know of a better way to write this query in MySQL?

Answer 1

You could try using an update-join.

UPDATE attempts
RIGHT JOIN
(
    SELECT id FROM attempts a1
    WHERE NOT EXISTS
    (
        SELECT 0 FROM attempts a2
        WHERE a2.mapID = a1.mapID AND 
            (a2.score > a1.score OR (a2.score = a1.score AND a2.date < a1.date))
    )
) tmp ON tmp.id = attempts.id
SET attempts.isHighestScore = 1;

If you put an index on the mapID column and score column this should be fairly fast.

Answer 2

You could try using a correlated subquery to only match rows for the same map where there is no other row with a greater score, or if there is one with an equal score, no other row with an older date:

UPDATE attempts
   SET isHighScore = 1
 WHERE ID IN (
           SELECT ID FROM (
               SELECT ID
                 FROM attempts a1
                WHERE NOT EXISTS (
                          SELECT 0
                            FROM attempts a2
                           WHERE a2.mapID = a1.mapID
                             AND (a2.score > a1.score OR
                                 (a2.score = a1.score AND a2.date < a1.date))
                      )
           ) a0
       )

A SQLFiddle.

Don't forget to set the old highscores to 0.

Answer 3

This works but I'm not sure about the performance, give a try. This is fiddle.

UPDATE attempts SET isHighScore = 1 WHERE ID IN
(
  SELECT ID FROM
  (
    SELECT a5.* FROM attempts a5
    INNER JOIN
    (
      SELECT mapID, max(score) as max_score, min(date) as min_date FROM
      (
         SELECT a1.id, a1.mapID, a1.score, a1.date 
         FROM attempts a1 
         INNER JOIN
        (
          SELECT mapID, max(score) as max_score 
          FROM attempts
          GROUP BY mapID
         ) a2
         ON
            a1.mapID = a2.mapID and a1.score = a2.max_score
      ) a3
      GROUP BY mapID
    ) a4
    ON a5.mapID = a4.mapID and a5.score = a4.max_score and a5.date = a4.min_date
   )a6
);

Explanation:

1- inner most group by gives the max_score and mapID for each mapID

2- this is joined with the original table to find the ids and dates of those attempts but may contain more than one row per mapID when there is a tie on max_score

3- once again group by mapID to get the min(date)

4- once again join with original table to get the ids for update

Answer 4

For anything like this, I always recommend a Temp table. In this case, the temp table could be having columns - (id, highest_score, map) -

create temporary table temp1
select min(id) as id, highest_score, map 
from attempts 
where <<attempts of today>>
group by map;

Now directly join attempts with the temp table and flag them.

update temp1 as t inner join attempts as a on a.id = t.id
set a.isHighestScore = 1;

Drop temporary table temp1;

(If replication is enabled, either use permanent tables or a transaction for being on safer side)