Hello, I'm currently developing an online game server(and the game itself) but I'm facing a difficulty.
If the game becomes popular and there are a million characters created(used and unused. there will always be characters not used by anyone anymore but I don't want to delete them incase their owner wants to return to the game), there will be issues.
Especially handling rank list of a million characters is a huge issue.
I want to know if there is a general method used by others or any other suggestions you guys have.
What I have tried:
I will try to explain the situation as clear as possible.
Just like many other projects, I plan on refreshing rank list with a specific time interval. I'm thinking of refreshing it every 5 minutes.
Ranks will be determined by users ELO(battle points, arena points, whatever you want to call it.)
After each fight , elo of winner and loser will be calculated and written to database.
And every 5 minutes I will execute below code to change ranks depending on user ELOs:
boost::shared_ptr<pqxx::work> w = DBConnector::getWork();
pqxx::result r = w->exec("select * from characters order by elo desc");
for (int i = 0; i < r.size(); i++)
{
long long userid = r[i]["userid"].as<long long>();
int charid = r[i]["charid"].as<int>();
w->exec("update characters set rank = " + std::to_string(i) + " where userid = " + std::to_string(userid) + " AND charid = " + std::to_string(charid));
}
w->commit();
The problem is, if there is a million characters, the whole process takes 2 minutes. This is too long.
What other alternatives do I have? Is there any solution that's used in general?