5

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 ?

Improve this question

1 Answer 1

Reset to default
5

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.

Improve this answer
2
  • There are certain factors like google page rank, website loading time, ranking from third party apis , whether its listed in dmoz or not , etc. using which I calculate score out of 100.00(float field ) for each website. This is a different table with website_id and factors based on which score is calculated. And then based on score I can update ranking in another table with website_id and rank. Is that the best way ?
    – Ashish Gupta
    Sep 4, 2015 at 17:34
  • 1
    Off hand, it seems like the best way.
    – Rick James
    Sep 4, 2015 at 17:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.