How many columns in a MySQL table

Question

How many fields should be in a MySQL table?

I have a form for receiving information from users, it is divided into 6 or 7 associations, but all are connected to each other. These are about 100 fields.

Do you recommend storing all of them in one table?

---

Thank you all for your answers, it’s good that my data in the table relates to some personal information of users, all I need to do is get them from the user, save and show them and have the editing option. Should I use normalization? Doesn't this increase the process time in my situation?

Answer 1

When giving you the following database normalization , you should usually be fine - although you may find some performance issues in the future.

It seems to me that there might be some kind of normalization?

In addition, you should consider how many of these columns will only have null values ​​and which naming conventions you use (and not just name, name2, etc.)

If you want to read it more .:

Data Normalization Basics

MySql :: Introduction to Database Normalization

Answer 2

MySQL supports up to 4096 columns in a table. Do not use a field in the main table if it often takes the value empty .

• if the column accepts NULL without problems, but if it is stored as "" its memory loss.

• Too often, null values ​​mean optional field data, so why store them in the main table.

This is what I meant in the statement.

Answer 3

There is no general rule. However, 100 columns definitely hint that your database design is wrong. Before continuing, you should read about normalizing and developing a database. I have a DB project with ~ 150 columns divided into almost 40 tables to give you an idea.

Answer 4

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

Answer 5

If they are all connected and cannot be normalized (not to mention anything> 1 is serialized), then I think that everything will be fine.

Are you sure that some things cannot be divided into several tables? Can you provide a sample of the information you collect?

Could you share things like

• user_details
• user_car_details
• ...

Answer 6

I would usually start dividing them into separate tables after 20-30 columns. However, this is really due to how you intend to use the data.

If you always need all the columns, then dividing them into separate tables will add unnecessary complexity and overhead. However, if there are groups of columns that are usually accessed together, then dividing along these rows with a ratio of 1-1 between tables can be useful.

This method can be useful if performance is a problem, especially if your table has large text columns.

---

Finally, I would repeat the other comments on the normalization posters. It is relatively rare to have 100 tables in one table, and maybe you need to normalize the table.

For example, the columns SETTING_1, SETTING_2, SETTING_3, SETTING_4 may be better in a separate table SETTINGS , which has a 1-lot relationship with the original table.

Answer 7

Thanks to everyone, so I think I’d better combine some of my data into one column, which will save me more space and reduce overhead

Answer 8

I noticed a performance loss of about 65 columns in the database with about 10,000 rows. But there may be a lack of server structure or something else.