Issues with a Table having 120 fields in mysql

Question

I am creating a table with 120 fields in MySQL. It will have 50 varchar(255) fields, other are maximum fields with char(1) , 1 date field and 5 double fields and others are int(11) fields.

My question is - will it cause any problem in future?

Answer 1

You need to stop and think about the design of the table. If you have that many columns, then chances are you may need to do some normalization of the table.

Although denormalized data can help you bypass the need to do JOIN queries to retrieve many columns of data, try to imagine what is happening to MySQL internally during data retrieval.

If a 120 column table undergoes SELECT * FROM ... WHERE, you would potentially cause the following:

• generate large temp tables
• fetch columns that are unnecessary
• handle many variable length fields

This would easily cause communication packets (sized by max_allowed_packet) to be pushed to the brink on every query.

In my earlier days as a developer, I worked at a company back in 1995 where DB2 was the main RDBMS. The company had a single table that had 270 columns, dozens of indexes, and had performance issues retrieving data. They contacted IBM and had consultants look over the architecture of their system, including this one monolithic table. The company was told "If you do not normalize this table in the next 2 years, DB2 will fail on queries doing Stage2 Processing (any queries requiring sorting on non-indexed columns)." This was told to a multi-trillion dollar company, to normalize a 270 column table.

Please see my other posts around this subject

• Jul 30, 2011 : More than 20 columns in database table MySQL
• Jul 20, 2011 : Too many columns in MySQL