What is the best way to update and keep track of ranks in an online game server.

See more:

C++

SQL

networking

PostgreSQL

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 is an array of results that is returned by sql query.
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?