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.