Take the 2-minute tour ×

I want to build a MySQL database for storing the ranking of a game every 1h. Since this database will become quite large in a short time, I figured it's important to have a proper design. Therefor some advice would be gratefully appreciated.

In order to keep it as small as possible, I decided to log only the first 1500 positions of the ranking. Every ranking of a player holds the following values:
ranking position, playername, location, coordinates, alliance, race, level1, level2, points1, points2, points3, points4, points5, points6, date/time

My approach was to simply grab all values of each top 1500 player every hour by a php script and insert them into the MySQL as one row. So every day the MySQL will grow 36,000 rows. I will have a second script that deletes every row that is older than 28 days, otherwise the database would get insanely huge. Both scripts will run as a cronjob.

The following queries will be performed on this data:

  • The most important one is simply the query for a certain name. It should return all stats for the player for every hour as an array.
  • The second is a query in which all players have to be returned that didn't gain points1 during a certain time period from the latest entry. This should return a list of players that didn't gain points (for the last 24h for example).
  • The third is a query in which all players should be listed that lost a certain amount or more points2 in a certain time period from the latest entry.

The queries shouldn't take a lifetime, so I thought I should probably index playernames, points1 and points2.

Is my approach to this acceptable or will I run into a performance/handling disaster? Is there maybe a better way of doing this?

share|improve this question
If you design your database properly, I don't think you would need to store playername, alliance or race. Those would simply be stored in the player table and will not be repeated in the ranking table. –  Ranhiru Cooray May 15 '11 at 2:26
36,000 rows a day is not a large number of rows for MySQL. When you get to a Million come back and start asking questions about performance. You are pre-maturely optimizing without profiling, and we all should know that is the root of all evil –  Jarrod Roberson May 15 '11 at 4:47
Ty every one. The reason why I thought, storing each row simply "as it comes in" would be better is that some players might be dropping out of the top 1500, while others would come in new. So when the cronjob does the inserts no comparisons and therefor no data needs to be fetched from the db. Otherwise I have to check every row that gets added for the existence of the username in the player table. Wouldn't that be more performance intensive? Also the max. php execution time will surely be an issue, so the script needs to be quick. The http fetch of the ranking site will be a time beast. –  x_squared May 15 '11 at 6:04
add comment

1 Answer

Here is where you risk a performance problem:

  • Your indexes will speed up your reads, but will considerably slow down your writes. Especially since your DB will have over 1 million rows in that one table at any given time. Since your writes are happening via cron, you should be okay as long as you insert your 1500 rows in batches rather than one round trip to the DB for every row. I'd also look into query compiling so that you save that overhead as well.

  • Ranhiru Cooray is correct, you should only store data like the player name once in the DB. Create a players table and use the primary key to reference the player in your ranking table. The same will go for location, alliance and race. I'm guessing that those are more or less enumerated values that you can store in another table to normalize your design and be returned in your results with appropriates JOINs. Normalizing your data will reduce the amount of redundant information in your database which will decrease it's size and increase it's performance.

  • Your design may also be flawed in your ranking position. Can that not be calculated by the DB when you select your rows? If not, can it be done by PHP? It's the same as with invoice tables, you never store the invoice total because it is redundant. The items/pricing/etc can be used to calculate the order totals.

  • With all the adding/deleting, I'd be sure to run OPTIMIZE frequently and keep good backups. MySQL tables---if using MyISAM---can become corrupted easily in high writing/deleting scenarios. InnoDB tends to fair a little better in those situations.

Those are some things to think about. Hope it helps.

share|improve this answer
Ty too. I have no experience in such large DBs, so I will rely on the expertise here. Surely there will be a lot more inserting than fetching. Only 5 people will use this DB a couple of times per day. To my shame I have to admit that I insert every row with one call to the DB in my test so far. I could probably improve it to inserting 100 at a time. But anything above is a problem. I can't fetch all the ranking data at the same time because of the max php execution time. So multiple scripts (rank 1 - 100, 101 - 200...) have to be called by the cronjob. Point 2 see comment above, 3 you're right –  x_squared May 15 '11 at 6:19
Regarding PHP's max execution time, I would just override that in your cron to be a number more reasonable for what you're doing: set_time_limit(5000); –  Jesse Bunch May 15 '11 at 17:38
add comment

Your Answer


