0

I want to know the fastest and most efficient way to compute points and ranking in real time.

I'm doing a betting football game (a game where users try to predict the outcome of a game before it). In my country is called "Quiniela".

Players put "predictions" just before the match starts. My question is: from the moment the game starts, which is the most efficient way (not just code level but to the database and server) to calculate the ranking for the players "real time" as their predictions regarding the actual outcome of the game.

For example:

If a Team1 vs Team2 plays, the user U1 predicted that the game would end 0-0, U2 predicted the game would end 1-0, U3 predicted the game would would end 1-1.

When starting the game, the U1 would be to first in the ranking because, if the match ends like this, his prediction would be correct, or at least the most accurate.

Then the score switches to 1-0, and the user U1 would be the last of the ranking (because his prediction is not possible anymore), U2 would be the first and U3 second.

If the score changes to 1-1, U2 would go first, U3 would be second (because he guessed part of the result) and U3 last.

I thought about creating a tree of possibilities with one level (0-0 first node, with 1-0 and 0-1 as child nodes) as you change the score, changes the Ranking "temporarily" and I would keep it in cache.

Once the game is over, I keep the latest Ranking in the database.

There is a more optimal way to solve this problem? It has been proposed before and already solved? I'm using MySQL for database, PHP (Cake Php) for the server and a bunch of mobiles who ask for the ranking once in a while.

Sorry for my lame english =(

Fingolricks
  • 1,196
  • 2
  • 14
  • 30
  • although the question is quite open, what I'm asking is if there is an algorithm that already do (which I did not see in college) that allows me to do so. It's like asking which is the best algorithm for sorting numbers. If it has a name (such as sorting algorithm, in this case) someone please tell me. – Fingolricks Jan 26 '15 at 02:30

1 Answers1

1

I would use the following approach:

Keep a ranking table where you store the current ranking after each gameday. Duplicate the table for a temporary ranking table. Whenever a gameday starts, copy the current ranking of the players into the temporary ranking table (you can avoid a second relation and the copying by introducing a temporary_score column into the original ranking table).

Whenever a gamescore changes, recalculate all scores. When the games are finished, you can just copy the temporary ranking back into the ranking table.

moo
  • 486
  • 8
  • 22
  • The problem is to update the "temporary column score" with every event when you have about 10 thousand users can overload the data base, which i would like to avoid. – Fingolricks Jan 26 '15 at 02:25
  • 1
    updating 10000 rows should not be a problem. I think you can do those "event-updates" entirely with SQL and stored procedures. – moo Jan 26 '15 at 02:28
  • Well, i'll try that and, if all explode, i'll ask you again. Thx man. – Fingolricks Jan 26 '15 at 02:34