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;
