I'll give you two solutions...
If things don't change too often, simply recompute the rank every time you fetch the 'profile' for the user. That is, do not pre-compute it or store it in the profile table. See code below.
How many users are there?
How often do you need to update highscore?
How often is the profile fetched?
Those questions may lead to optimization choices that may be necessary if the system is "too busy".
The following should give you the rank, but at the cost of a couple of table scans and a sort:
SELECT Rank
FROM
( SELECT tbl.name,
(@rank := @rank + 1) AS Rank
FROM
( SELECT @rank := 0 ) x -- initialize the variable
JOIN tbl
ORDER BY highscore DESC -- sorting occurs before incrementing @rank
) y
WHERE name = ? -- fill in with desired user
LIMIT 1 -- a minor optimization
The `y` subquery computes the Rank for each name. To periodically recompute everyone's rank, I would do something like:
CREATE TABLE NewRanks ( PRIMARY KEY (name) ) ENGINE=InnoDB AS
SELECT tbl.city, (@rank := @rank + 1) AS Rank
FROM
( SELECT @rank := 0 ) x
JOIN tbl
ORDER BY highscore DESC;
RENAME TABLE Ranks TO OldRanks, NewRanks TO Ranks;
DROP TABLE OldRanks;
This would rebuild a table named `Ranks`,...
* Indexed for efficient access.
* The RENAME is atomic, so you can always access it, even while it is being rebuilt.
* You can JOIN Ranks to the Profile table very efficiently.
* This would make the profile fetching much faster than my first solution.
* However, there will be a delay between updating highscore and posting the new Rank.
Because of heavy access to these tables, you should be using InnoDB, not MyISAM.