How do I combine these two queries to calculate rank change?

Question

Introduction

I have a highscore table for my game which uses ranks. The scores table represents current highscores and player info and the recent table represents all recently posted scores by a user which may or may not have been a new top score.

The rank drop is calculated by calculating the player's current rank minus their rank they had at the time of reaching their latest top score.

The rank increase is calculated by calculating the player's rank they had at the time of reaching their latest top score minus the rank they had at the time of reaching their previous top score.

Finally, as written in code: $change = ($drop > 0 ? -$drop : $increase);

Question

I am using the following two queries combined with a bit of PHP code to calculate rank change. It works perfectly fine, but is sometimes a bit slow.

Would there be a way to optimize or combine the two queries + PHP code?

I created an SQL Fiddle of the first query: http://sqlfiddle.com/#!9/30848/1

The tables are filled with content already, so their structures should not be altered.

This is the current working code:

$q = "
            select
            (
            select
                coalesce(
                    (
                        select count(distinct b.username)
                        from recent b
                        where
                            b.istopscore = 1  AND
                            (
                                (
                                    b.score > a.score AND
                                    b.time <= a.time
                                ) OR
                                (
                                    b.score = a.score AND
                                    b.username != a.username AND
                                    b.time < a.time
                                )
                            )
                        ), 0) + 1 Rank
            from scores a
            where a.nickname = ?) as Rank,
            t.time,
            t.username,
            t.score
            from
            scores t
            WHERE t.nickname = ?
            ";

            $r_time = 0;

            if( $stmt = $mysqli->prepare( $q ) )
            {
                $stmt->bind_param( 'ss', $nick, $nick );
                $stmt->execute();
                $stmt->store_result();
                $stmt->bind_result( $r_rank, $r_time, $r_username, $r_score );

                $stmt->fetch();

                if( intval($r_rank) > 99999 )
                    $r_rank = 99999;

                $stmt->close();
            }

            // Previous Rank
            $r_prevrank = -1;

            if( $r_rank > -1 )
            {
                $q = "
                select
                    coalesce(
                        (
                            select count(distinct b.username)
                            from recent b
                            where
                                b.istopscore = 1  AND
                                (
                                    (
                                        b.score > a.score AND
                                        b.time <= a.time
                                    ) OR
                                    (
                                        b.score = a.score AND
                                        b.username != a.username AND
                                        b.time < a.time
                                    )
                                )
                            ), 0) + 1 Rank
                from recent a
                where a.username = ? and a.time < ? and a.score < ?
                order by score desc limit 1";

                if( $stmt = $mysqli->prepare( $q ) )
                {
                    $time_minus_one = ( $r_time - 1 );

                    $stmt->bind_param( 'sii', $r_username, $time_minus_one, $r_score );
                    $stmt->execute();
                    $stmt->store_result();
                    $stmt->bind_result( $r_prevrank );

                    $stmt->fetch();

                    if( intval($r_prevrank) > 99999 )
                        $r_prevrank = 99999;

                    $stmt->close();
                }
                $drop = ($current_rank - $r_rank);
                $drop = ($drop > 0 ? $drop : 0 );


                $increase = $r_prevrank - $r_rank;
                $increase = ($increase > 0 ? $increase : 0 );

                //$change = $increase - $drop;
                $change = ($drop > 0 ? -$drop : $increase);
            }

            return $change;

Answer 1

If you are separating out the current top score into a new table, while all the raw data is available in the recent scores.. you have effectively produced a summary table.

Why not continue to summarize and summarize all the data you need?

It's then just a case of what do you know and when you can know it:

• Current rank - Depends on other rows
• Rank on new top score - Can be calculated as current rank and stored at time of insert/update
• Previous rank on top score - Can be transferred from old 'rank on new top score' when a new top score is recorded.

I'd change your scores table to include two new columns:

• scores - id, score, username, nickname, time, rank_on_update, old_rank_on_update

And adjust these columns as you update/insert each row. Looks like you already have queries that can be used to backfit this data for your first iteration.

Now your queries become a lot simpler

To get rank from score:

SELECT COUNT(*) + 1 rank
  FROM scores 
 WHERE score > :score

From username:

SELECT COUNT(*) + 1 rank
  FROM scores s1
  JOIN scores s2
    ON s2.score > s1.score
 WHERE s1.username = :username

And rank change becomes:

  $drop = max($current_rank - $rank_on_update, 0);
  $increase = max($old_rank_on_update - $rank_on_update, 0);
  $change = $drop ? -$drop : $increase;

UPDATE

• Comment 1 + 3 - Oops, may have messed that up.. have changed above.
• Comment 2 - Incorrect, if you keep the scores (all the latest high-scores) up to date on the fly (every time a new high-score is recorded) and assuming there is one row per user, at the time of calculation current rank should simply be a count of scores higher than the user's score (+1). Should hopefully be able to avoid that crazy query once the data is up to date!

If you insist on separating by time, this will work for a new row if you haven't updated the row yet:

SELECT COUNT(*) + 1 rank
  FROM scores 
 WHERE score >= :score

The other query would become:

SELECT COUNT(*) + 1 rank
  FROM scores s1
  JOIN scores s2
    ON s2.score > s1.score 
    OR (s2.score = s1.score AND s2.time < s1.time) 
 WHERE s1.username = :username

But I'd at least try union for performance:

SELECT SUM(count) + 1 rank
  FROM ( 
    SELECT COUNT(*) count
      FROM scores s1
      JOIN scores s2
        ON s2.score > s1.score
     WHERE s1.username = :username
     UNION ALL
    SELECT COUNT(*) count
      FROM scores s1
      JOIN scores s2
        ON s2.score = s1.score
       AND s2.time < s1.time
     WHERE s1.username = :username
       ) counts

An index on (score, time) would help here.

Personally I'd save yourself a headache and keep same scores at the same rank (pretty standard I believe).. If you want people to be able to claim first bragging rights just make sure you order by time ASC on any score charts and include the time in the display.