Hi all,
how many columns in a table is to many, my character table holds 24 columns and counting.
How much will this slow things down?
rick out
how many columns
-
Jackolantern
- Posts: 10891
- Joined: Wed Jul 01, 2009 11:00 pm
Re: how many columns
You probably could never add enough columns to a table to slow it down. Your traditional business, such as a telecom company, has the most complex databases out there, and I have personally seen tables with 500+ columns run just fine.
However, specifically for a game, when you start to get over about 10 - 15 columns, you really should start thinking of breaking some things down. Not because MySQL will slow down or choke up (it doesn't care hardly at all), but because you are probably lumping too much data into one "object" and it can make extending, updating or even maintaining your game more difficult later. My very first PHP game I attempted had just a "user" table that had the player's info (email, password, etc.) along with their character info (name, race, class) as well as their stats (Strength, Health, etc.). Each of these three things should likely be their own table with their own IDs to function as foreign keys to relate them. I mentioned this the other day, but as an example of why you would not want to lump all of that info together, say you decided one day that you wanted to allow each user to have more than one character on their account. If you have both the user info, like email and login data, with the character data, you would have to manually split them, hope you don't mess something up and invalidate your entire database, and then go change who knows how many queries to be updated to the new setup. However, if you had been thinking small in the first place, and had kept separate entities in separate tables, changing this would probably take you 30 minutes max.
When in doubt, keep data in separate tables and simply relate them. This keeps your database flexible, and also protects your data. The more data you make accessible to certain parts of code, the more chances an errant part of your code will corrupt said data. For example, when someone logs in, only use the login data to carry that out. If the login data is attached to the character data, you are having to make that character data accessible to the login code for no reason, and that is just more data that could get messed up.
However, specifically for a game, when you start to get over about 10 - 15 columns, you really should start thinking of breaking some things down. Not because MySQL will slow down or choke up (it doesn't care hardly at all), but because you are probably lumping too much data into one "object" and it can make extending, updating or even maintaining your game more difficult later. My very first PHP game I attempted had just a "user" table that had the player's info (email, password, etc.) along with their character info (name, race, class) as well as their stats (Strength, Health, etc.). Each of these three things should likely be their own table with their own IDs to function as foreign keys to relate them. I mentioned this the other day, but as an example of why you would not want to lump all of that info together, say you decided one day that you wanted to allow each user to have more than one character on their account. If you have both the user info, like email and login data, with the character data, you would have to manually split them, hope you don't mess something up and invalidate your entire database, and then go change who knows how many queries to be updated to the new setup. However, if you had been thinking small in the first place, and had kept separate entities in separate tables, changing this would probably take you 30 minutes max.
When in doubt, keep data in separate tables and simply relate them. This keeps your database flexible, and also protects your data. The more data you make accessible to certain parts of code, the more chances an errant part of your code will corrupt said data. For example, when someone logs in, only use the login data to carry that out. If the login data is attached to the character data, you are having to make that character data accessible to the login code for no reason, and that is just more data that could get messed up.
The indelible lord of tl;dr