How many columns in MySQL table

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? 开发者_JS百科doesn't it increase the process time in my situation?

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

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.

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.

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.

If they all relate and can not be normalised (not to mention nothing > 1 is serialised) then I guess it will be OK.

Are you sure some things can't be split into multiple tables though? Can you provide a sample of the information you are gathering?

Could you split things into such as

• user_details
• user_car_details
• ...

I would typically start splitting them into separate tables after around 20-30 columns. However, it is really driven by how you are going to use the data.

If you always need all the columns, then splitting them out into separate tables will add unnecessary complication and overhead. However, if there are groups of columns that tend to be accessed together, then splitting along those lines with a 1-1 relationship between the tables might be useful.

This technique can be useful if performance is a concern, especially if you have large text columns in your table.

Lastly, I would echo the other posters comments about normalisation. It is relatively rare to have 100s of columns in one table and it may be that you need to normalise the table.

For example columns, SETTING_1, SETTING_2, SETTING_3, SETTING_4 might be better in a separate SETTINGS table, which has a 1-many relationship to the original table.

Thank you all,so i think it's better for me to merge some of my data into one column,which save me more space and decrease overhead

I've noticed a performance loss around 65 Columns in a DB with around 10 000 Rows. But could be lack of serverstructure or something else.