mysqlsqlnosqlmmo

What’s the best way to handle an 'very large' inventory with SQL?

I am looking into building an online card game like that of Hearthstone. Players will have an unlimited inventory to store all the cards they own.

With this method there would be a very large their amount of data 1 row for every player card combination thus growing exponentially.

Originally, I wanted to have 3 tables:

Then I could use a statement like SELECT Card_ID FROM Inventory_Table WHERE Player_ID=YourID

Obviously, there is a scaling issue here where the more cards I add, and the more players join the longer this will take to get your card list.

I was thinking about using something like MongoDB as a NoSQL alternative to help with the potential performance issues that this would cause but then I found out its not free for commercial use unlike mySQL so I abandoned that plan.

The 3rd and final idea I came up with was dynamically adding tables. when a player is created (creates an account) I could just add a table with the name "Player_Cards_" + Player_ID (E.G. Player_Cards_318) Something is telling me this is a bad idea but I'm not sure.

Please could someone point me in the right direction please.

Solution

  • Millions of rows in a table is usually not a problem. Billions of rows gets exciting, but not necessarily impossible. Please do the math and come up with a crude estimate.

    Meanwhile, please provide SHOW CREATE TABLE so we know the datatypes, engine, and indexes involved.

    Do not create a new table for each user (or each whatever). This is an often asked question; the answer is always "no".

    SELECT Card_ID FROM Inventory_Table WHERE Player_ID=YourID

    Is a very basic query. Any index starting with Player_ID will allow for executing that query very fast (milliseconds). INDEX(Player_ID, Card_ID) is likely to be even faster. What other common queries will you have?

    Scaling... A simple Rule of Thumb is "100 bytes per row". Calculate how many bytes you will need for all the rows in all the tables; will that fit on the disk you have? (I suspect it will.)

    You will be reaching into 2 or 3 of those tables in a single SELECT. So learn how to do an SQL JOIN.

    Re "logarithmic": A "point query" in a trillion rows will take about twice as long as in a million rows.

    My opinion of "no sql" -- You have to re-invent SQL to get the task done. In the process, you will learn a lot about optimization techniques that an RDBMS would otherwise simply do for you.