How many columns in MySQL table

Question

How many fields are OK to have in a MySQL table?

I have a form to get information from users, it is divided into 6 or 7 unites, but all related to each other. They are about 100 fields.

Do you recommend to keep all of them in one table?

---

Thank you all for your responses,well my table's data are about some personal information of users,all the work that is needed to be done is to get them from the user,save and show them and have the edit option. should i use the normalization? doesn't it increase the process time in my situation?

Answer 1

Providing you are following database normalization, you generally should be ok - although you may find some performance issues down the road.

To me, it seems like perhaps there could be some normalization?

Also, you should consider how many of these columns will just have null values, and what naming conventions you are using (not just name, name2 etc)

In case you want to read into it more.:

Data normalization basics

MySql :: An introduction to database normalization

Answer 2

MySQL supports up to 4096 columns per table. Dont use a field in main table if it often takes empty values.

1. if a column takes NULL no problem but if one stored as " " its memory wastage.

2. Too often null values means optional field data then why should one store it in main table.

Thats what i meant in the statement.

Answer 3

For data that does not need to be indexed or searched I create a single column in my database like col_meta which holds a serialized PHP array of values, it saves space and can expand or contract as needed. Just a thought.

Answer 4

There's no general rule to that. However, 100 columns definitely hints that your DB design is plain wrong. You should read about normalization and database design before continuing. I have a DB design with ~150 columns split up into nearly 40 tables, just to give you an idea.