0

I have a table that looks like this. Learning sql recently,

USERID|MATCHID|TOURNAMENTID|SCORE|COUNTRY

There are different tournaments running day to day and each tournament has many matches. some users belong to the same match which they compete with each other. I keep each individual user's score for a match like this. So for example if I want to create a leaderboard for a match, I query all rows matching the MATCHID and get all users score that is assigned to that match.

My question is, this table will have 10s of milllions of entries. I use mysql, and I read about sharding. I gave the table above the name Leaderboard. Moreover, the country field indicates the user's country that is playing in the match.

I will query users' belonging to a country and a tournamentid to create country based leaderboards. Also I will query matchid which might have users from different countries. If the countries are such as UK, USA, and Germany; would it be smart to divide this table into 3 tables such as,

GermanyLeaderboard,
USALeadearboard,
UKLeaderboard.

I heard it is not a good design to hardcode tables(table names) like this, but isn't this the point of geographical sharding ? so that I can query faster especially when I want to query the users of a specific country. Yet If I want to query a matchid, I would need to simultaneously query 3 different tables, which might indeed be faster. What is your opinion ? Thank you

Extra question: Sharding has no use if the table's data is not split across different servers right? I mean, if i shard the table into multiple shards on the same machine(cpu/memory), it has no meaning, or doesn't make faster queries; since I can already use indexes, correct?

7
  • There are different tournaments running day to day and each tournament has many matches. If so then MATCHID value uniquely defines TOURNAMENTID value, is it? And if so then TOURNAMENTID column is excess (overdefining, denormalization) and it should be removed.
    – Akina
    Commented Apr 4 at 4:51
  • this table will have 10s of milllions of entries. I use mysql, and I read about sharding 100kk rows is average dataset size for MySQL, and I doubt that you need in sharding or even in partitioning. Proper indexing is much more important-think about it. Also you may think about pre-calculation. When match scoring finished then you execute a query or SP which creates leadership info and saves it into separate table. When this info is needed you may get it from this table w/o any calculation, and this is fast enough. Of course if it have not been created yet then gather it from the src table.
    – Akina
    Commented Apr 4 at 4:56
  • @Akina no, I will have 5 users in a match, so MATCHID will appear 5 times in the db, but TOURNAMENTID will appear count of matches * 5, if that makes sense
    – umarkaa
    Commented Apr 4 at 5:30
  • Correct, I need a leadership info, I will need seperate tables for match leadership and country total points.
    – umarkaa
    Commented Apr 4 at 5:31
  • @Akina check this, harcore query for me. dba.stackexchange.com/questions/338317/…
    – umarkaa
    Commented Apr 4 at 7:43

0

Your Answer

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

Browse other questions tagged or ask your own question.