CODEWITHSUNDEEP
mysqldatabase-design
Motive
Motive

Reputation: 3111

Too many fields in MySQL?

I developed a stats site for a game as a learning project a few years back. It's still used today and I'd like to get it cleaned up a bit.

The database is one area that needs improvement. I have a table for the game statistics, which has GameID, PlayerID, Kills, Deaths, DamageDealt, DamageTaken, etc. In total, there are about 50 fields in that single table and many more that could be added in the future. At what point are there too many fields? It currently has 57,341 rows and is 153.6 MiB by itself.

I also have a few fields that stores arrays in a BLOB in this same table. An example of the array is Player vs Player matchups. The array stores how many times that player killed another player in the game. These are the bigger fields in filesize. Is storing an array in a BLOB advised?

The array looks like:

        [Killed] => Array
            (
                [SomeDude] => 13
                [GameGuy] => 10
                [AnotherPlayer] => 8
                [YetAnother] => 7
                [BestPlayer] => 3
                [APlayer] => 9
                [WorstPlayer] => 2
            )

These tend to not exceed more than 10 players.

Upvotes: 7

Views: 1099

Answers (4)

Michael Riley - AKA Gunny
Michael Riley - AKA Gunny

Reputation: 5184

There's nothing wrong with adding columns to a database table as time goes on. Database designs change all the time. The thing to keep in mind is how the data is grouped. I have always treated a database table as a collection of like items.

Things I consider are as follows:

When inserting data into a row how many columns will be null?
Does this new column apply to 80% of my data that is already there?
Will I be doing several updates to a few columns in this table?
If so, do I need to keep track of what the previos values were just in case?

By thinking about you data like this you may discover that you need to break your table up into a handful of separate smaller tables linked together by foreign keys.

Upvotes: 1

Ray
Ray

Reputation: 41488

With mysql you've got a hard limit of roughly 4000 columns (fields) and 65Kb total storage per row. If you need to store large strings, use a text field, they're stored on disk. Blobs really should be reserved for non-textual data (if you must).

Don't worry in general about the size of your db, but think about the structure and how it's organized and indexed. I've seen small db's run like crap.

If you still want numbers, when you're total DB gets in the GB range or past a couple hundred thousand rows in a single table, then start worrying more about things--150M in 60K rows isn't much and table scans aren't going to cost you much in performance. However, now's the time to make sure you create good covering indexes on your heavily used queries.

Upvotes: 1

Yevgeny Simkin
Yevgeny Simkin

Reputation: 28389

I prefer to not have one table with an undetermined number of columns (with more to come) but rather to have an associated table of labels and values, so each user has an id and you use that id as a key into the table of labels and values. That way you only store the data you need per user. I believe this approach is called EAV (as per Triztian's comment) and it's how medical databases are kept, since there are SO many potential fields for an individual patient, even while any given patient only has a very small number of those fields with actual data.

so, you'd have 

user:
id | username | some_other_required_field

user_data:
id | user_id | label | value

Now you can have as many or as few user_data rows as you need per user.

[Edit]

As to your array, I would treat this with a relational table as well. Something like:

player_interraction:
id | player_id | player_id | interraction_type

here you would store the two players who had an interaction and what type of interaction it was.

Upvotes: 2

Prescott
Prescott

Reputation: 7412

The table design seems mostly fine. As long as the columns you are storing can't be calculated from the other columns within the same row. IE, you're not storing SelfKills, OtherDeath, and TotalDeaths (where TotalDeaths = SelfKills + OtherDeath). That wouldn't make sense and could be cut out of your table.

I'd be curious to learn more about how you are storing those Arrays in a BLOB - what purpose do they serve in a BLOB? Why aren't they normalized into a table for easy data transformation and analytics? (OR are they and they are just being stored as an array here for easy of data display to end users).

Also, I'd be curious how much data your BLOB's take up vs the rest of the table. Generally speaking, the size of the rows isn't as big of a deal as the number of rows, and ~60K is no big deal at all. As long as you aren't writing queries that need to check every column value (ideally you're ignoring blobs when trying to write a where clause).

Upvotes: 1

Related Questions