How to optimize a score/rank table with different specific scores and ranks?

Question

in our project we've got an user table where userdata with name and different kind of scores (overall score, quest score etc. is stored). How the values are calculated doesn't matter, but take them as seperated.

Lets look table 'users' like below

  id   name   score_overall   score_trade   score_quest    

  1    one    40000           10000         20000
  2    two    20000           15000         0
  3    three  30000           1000          50000
  4    four   80000           60000         3000

For showing the scores there are then a dummy table and one table for each kind of score where the username is stored together with the point score and a rank. All the tables look the same but have different names.

id  name  score  rank  

They are seperated to allow the users to search and filter the tables. Lets say there is one row with the player "playerX" who has rank 60. So if I filter the score for "playerX" I only see this row, but with rank 60. That means the rank are "hard stored" and not only displayed dynamically via a rownumber or something like that.

The different score tables are filled via a cronjob (and under the use of a addional dummy table) which does the following:

1. copies the userdata to a dummy table
2. alters the dummy table by order by score
3. copies the dummy table to the specific score table so the AI primary key (rank) is automatically filled with the right values, representing the rank for each user.

That means: Wheren there are five specific scores there are also five score tables and the dummy table, making a total of 6.

How to optimize?

What I would like to do is to optimize the whole thing and to drop duplicate tables (and to avoid the dummy table if possible) to store all the score data in one table which has the following cols:

userid, overall_score, overall_rank, trade_score, trade_rank, quest_score, quest_rank

My question is now how I could do this the best way and is there another way as the one shown above (with all the different tables)? MYSQL-Statements and/or php-code is welcome. Some time ago I tried using row numbers but this doesn't work a) because they can't be used in insert statements and b) because when filtering every player (like 'playerX' in the example above) would be on rank 1 as it's the only row returning.

Answer 1

Well, you can try creating a table with the following configuration:

id | name | score_overall | score_trade | score_quest | overall_rank | trade_rank | quest_rank

If you do that, you can use the following query to populate the table:

SET @overall_rank:=-(SELECT COUNT(id) FROM users);
SET @trade_rank:=@overall_rank;
SET @quest_rank:=@overall_rank;

SELECT *
FROM users u
INNER JOIN (SELECT id,
                   @overall_rank:=@overall_rank+1 AS overall_rank
            FROM users
            ORDER BY score_overall DESC) ovr
ON u.id = ovr.id
INNER JOIN (SELECT id,
                   @trade_rank:=@trade_rank+1 AS trade_rank
            FROM users
            ORDER BY score_trade DESC) tr
ON u.id = tr.id
INNER JOIN (SELECT id,
                   @quest_rank:=@quest_rank+1 AS quest_rank
            FROM users
            ORDER BY score_quest DESC) qr
ON u.id = qr.id
ORDER BY u.id ASC

I've prepared an SQL-fiddle for you.

Although I think performance will weigh in if you start getting a lot of records.

A bit of explanation: the @*_rank things are SQL variables. They get increased with 1 on every new row.