Best database design for website ranking I am working on a project in which I need to rank websites in two way - Global rank and their rank in a particular country. There are various factors based on which both are calculated. There are ~200 countries for which rank needs to be calculated.

I am using MySQL

I have two questions - 

Global rank

Whenever any new website is added -


Either I need to recalculate the ranks for all the website if I store the website rank in database. This requires a lot of processing each time a new website is added.
or the other option is to assign a score to each website and whenever query is made assign rank by doing order by based on score, and returning its position. This doesn't seem scalable way as order by is expensive operation.


Country Rank database and updation


How should I store per country rank of each website ?
How to update all these ranks when a new website is added ? Here the updation will be even more costly than in case of Global rank.
Should I generate it on-the-fly when queried and then cache the result ?


How do websites like play store show personalized and country based results ?
Will you be ranking the billions of web sites?  If so, then on-the-fly and recompute-all are out of the question.

Instead, have a background task working on the problem.  For display, show the info that is in the table, possibly out of date by a day or two (depending on how long it takes to recalc).

Put the ranking in a separate table, with only website_id and rank.  This will make replacing the entire table after the recalc atomic and fast.  Perhaps a second table with website_id, country_code (CHAR(2) CHARACTER SET ascii), and rank.

You have not explained how the ranking is calculated, so I can't give you any insight in how to do it as a background task.