Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Just out of curiosity: what sort of data do you plan to store in this table? –  dezso Dec 15 '12 at 11:22
1  
The number of fields is not really a problem, but the description here suggests the table might be highly de-normalized. If you've got repeating field groups or large number of nullable fields, you're probably doing something that will result in a huge nightmare for anybody trying to do something useful with your database. –  Bacon Bits Dec 15 '12 at 14:42
add comment

1 Answer

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

share|improve this answer
1  
I think it would be better solution if I normalize the table. So I am noramalizing the table. –  Ripa Saha Dec 17 '12 at 6:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.