mysql - Can having too many fields be inefficient?

[Red Squirrel]

I have an application which consists of a couple thousand different object types, which represent items in a game. Each type has unique variables that need to be saved to disk. From a DB perspective, bigints, and text (ex: datetime would be converted to unix time stamp and so on).

Currently, all this data is serialized into one big string and is put into a text field. There are about 4 tables that consist of the major object types (Item, Mobiles, etc) but each of these type have many children types There are a few other fields such as the serial of the item, the type, and other "system" info.

I need to rewrite my code which handles the saving process, and this rewrite will make it possible to actually save all the values in their own independent fields. Some of these types could easily have 100 fields. Is there a point where there are just too many fields in a table? Am I better off just keeping it as it is? I would keep the 4 tables and they would just have every field to handle the various types, so a lot of them would be blank. Am I better off breaking it into a table per type (we're talking thousands of tables here)

Just wondering what you think would be more efficient, leave it as is, as a large serialized string or actually separate into separate fields. Also should I keep the 4 tables or have a table per object type?

This app is coded in C#. The SQL portion currently works fine, so I'm thinking of sticking with what I have, I just need to change the preprocessing portion that is unrelated to SQL, but figured while I'm there I could change the way it saves to SQL too.

[Alsvha]

It really depends on how you need to use the data (I'm always very data-driven, so how I'll use my data decides how I'll design).

The advantage of splitting stuff up into columns/fields means you can search through it much easier and much faster.
It'll also enable you to normalize your model where useful and thereby simplify your model and save some space etc (although, do not over-normalize, find a happy medium between performance and normalization).

Is there a point where there are just too many fields in a table?
I'm sure there is a point, but I wouldn't worry about "100 fields" in a table, although it is many. Chances are some of it should/can be normalized out into other sub tables and then just be referenced.


Am I better off breaking it into a table per type (we're talking thousands of tables here)
A thousand tables also sound like a lot, but perhaps your data model is just that big, but chances are you're missing some data model design. .


It is difficult to give concrete advice in such a situation because it requires a lot more insight into the model and the data and your project in question.

But I do not see many advantages keeping the values concatenated in one field just to save on the tables and columns.

[Red Squirrel]

In my case I'm not too worried as far as data normalization goes. The app simply writes to the DB so the data is physically somewhere, and never reads from it. When the app starts it does a select * to load all the data. I'm basically using it as a bulk storage medium, so to speak.

So I'm more worried about if any of these methods may lead to performance issues, or if I might hit some kind of limit such as too many collumns or too many tables. I'm personally aiming for multiple tables (1000's) but I'm just scared there is some kind of limit, as this app grows and grows, and more types and fields are constantly being added.

The beauty of normalizing it slightly more then my current serialized system is that I at least do have the option to query if I need to. Good for troubleshooting stuff in my app and what not, or doing reporting. Such queries would probably be done in a "warehouse" environment and not live though.