Dynamic Highscore Table

Anyone an idea?

Can you give an example? What makes the query very complex for a high scores table?

Yeah, I'm not understanding what would be complex about a highscore table. Wouldn't it just be.. a score, and maybe a user reference or something?

Does it really need to be any more difficult than this? (Top 10 scores)

SELECT * FROM scores ORDER BY score DESC LIMIT 0,10

So you would create a highscore table with where every user is inserted and update the score info?

I normally create a MySQL VIEW (dynamic) where it gets me alle users (just some data like id, name) and the I collect all "actions" of every user (action could be something like winning a game, maybe there are different game types where you get different scores), and then I show maybe just different kind of users based on a team they are in or their role (admin or not) etc. This is quite complex and having a MySQL VIEW helps to visualize the highscores.

It's only as complex as you make it. :)

It all really depends on what you're trying to accomplish I guess.

I suppose you are referring to a score number that relies on a complex algorithm rather than a simple integer?

What I do for those is having a column (if it takes multiple columns I go with a different table) for that information, and every time something happens that would change the result, I trigger an event and re eval the algo just for that user and stores the new score.

With that approach, once I have to do the query, I don't have to run the algo for every user, it's already computed, so I only have to do an ordering by the final result.

Somehow, as the algo gets more complex, results have 1 million reasons to loose sync with the result it "should have", so I usually run a backup Cron daily to recompute the algo for every active user (inactive users triggers a recompute event once the log back in).

As the apps get more complex, you start using Queues and more regular Crons to maintain the Algo results updated without overloading servers.

What I refer to is that in the past I didn't like to save a fix number for points a user has. This is because when this update has an error I have no glue how to recalculate the points again for this user. This is also why I normally store "actions" for like winning a game or badge an then based on (sometimes) not simple algo i calculate the points when the highscore is needed by a request.

Never though of checking the points of every user in a cronjob. Maybe that's an option here. I guess the query would be much faster too when I don't have to calculate the points on request.

One year ago and I am still not satisfied with the replies, sry :-)

It is like that:

I have games and users and scores and ranks. So what I always have is an action table where I save everything for what you can get points:

actions
____________________________
id  action_name points

and of course an actions_users table for saving all the actions for the users. This is also important so I can show the user for what he has achieved points.

actions_users
_______________________
id  user_id action_id

But what I find tricky is score and rank to calculate a highscore.

score = sum of action points for a user

rank = depends on the score of all the other users

So I don't want to save points and rank to the user, because ranks will likely change for all users. In the past I have worked with Mysql views where dynamically the highscore data is calculated. So I can easy see a visual highscore in my sql client. But I don't see webviews that much anymore and I'm also not really satisfied with them.

Since I don't think I am the only working with scores and ranks and highscore I am really interested in other approaches.

Guess one of the keypoints is that I need to calculate the ranks for all users again every time someone gets new points. (+10.000 users)