How to deal with big tables in MySQL? (DB design for a Game)

Suppose I have a Games table, a Players table, and a Users table.

• Each Game has many Players
• Each Player has a status, like 'dead' or 'alive'. So players are not users.
• Each Player has a User

The structure looks fine so far. But I was wondering, if there may b开发者_JAVA技巧e thousands of games a day (this kind of game is very short in duration), and each game has 10 or 20 players.. I may end up with millions of rows in the Players table in less than a year. And I need to keep each player stored in the table even after the game ended because I want to be able to replay any game. I'm worried about the performance at that point, selects and updates will become slower and slower, right?

Any thoughts?

Essentially it is a question of scalability. Because scalability is a problem almost all popular web sites, games, etc. encounter, there is a range of solutions to that. First of all, given a reasonable database design and use of indexes, modern databases can handle millions of rows of data fine. If your game is so popular that the about of data grows beyond what modern databases can handle and you have some business model to your enterprise, you would probably be earning enough to hire top–notch experts to help you with that problem.

If you are just starting implementing the game, I would suggest you leave the fine–tuning of the database and queries for later, odds are performance bottlenecks will be in different places than where you expect them to be. Do not optimize prematurely :)

Yes after long time , there would be some problem regarding performance, but a proper Indexing on these tables-fields would make it quite easier for you.

Track all the upcoming select and update queries on your tables and do proper indexing.

you can refer How MySQL Uses Indexes and EXPLAIN Output Format

You can also think some logic to archive some games or records after some time(like 1 month or 2 month) into the another tables with same structure.