Is there a performance decrease if there are too many columns in a table?

Question

Is there a performance cost to having large numbers of columns in a table, aside from the increase in the total amount of data? If so, would splitting the table into a few smaller ones help the situation?

Solution 1

If you really need all those columns (that is, it's not just a sign that you have a poorly designed table) then by all means keep them.

It's not a performance problem, as long as you

• use appropriate indexes on columns you need to use to select rows
• don't retrieve columns you don't need in SELECT operations

If you have 30, or even 200 columns it's no problem to the database. You're just making it work a little harder if you want to retrieve all those columns at once.

But having a lot of columns is a bad code smell; I can't think of any legitimate reason a well-designed table would have this many columns and you may instead be needing a one-many relationship with some other, much simpler, table.

Solution 2

I don't agree with all these posts saying 30 columns smells like bad code. If you've never worked on a system that had an entity that had 30+ legitimate attributes, then you probably don't have much experience.

The answer provided by HLGEM is actually the best one of the bunch. I particularly like his question of "is there a natural split....frequently used vs. not frequently used" are very good questions to ask yourself, and you may be able to break up the table in a natural way (if things get out of hand).

My comment would be, if your performance is currently acceptable, don't look to reinvent a solution unless you need it.

Solution 3

I'm going to weigh in on this even though you've already selected an answer. Yes, tables that are too wide could cause performance problems (and data problems as well) and should be separated out into tables with one-one relationships. This is due to how the database stores the data (well at least in SQL Server not sure about MySQL but it is worth doing some reading in the documentation about how the database stores and accesses the data).

Thirty columns might be too wide and might not, it depends on how wide the columns are. If you add up the total number of bytes that your 30 columns will take up, is it wider than the maximum number of bytes that can be stored in a record?

Are some of the columns ones you will need less often than others (in other words is there a natural split between required and frequently used info and other stuff that may appear in only one place not everywhere else), then consider splitting up the table.

If some of your columns are things like phone1, phone2, phone3 - then it doesn't matter how many columns you have you need a related table with a one-to-many relationship instead.

In general, though 30 columns are not unusually big and will probably be OK.

Solution 4

Technically speaking, 30 columns is absolutely fine. However, tables with many columns are often a sign that your database isn't properly normalized, that is, it can contain redundant and / or inconsistent data.

Solution 5

Should be fine, unless you have select * from yourHugeTable all over the place. Always only select the columns you need.

View more solutions

Comments

[snowflake]

I see one reason that I consider legitimate: when loading a legacy or proprietary (indexed or csv) file in a table to leverage the database power in order to exploit it.

[FallenAngel]

I read such docs long ago, and i know about them... But as i said, mostly used normalization forms are first three. The rest is not commonly used. My porpose was show some general info about normalization. And yes it tells about 8, but its really hard to find information about normalization beyond 5th normal form and BCNF and DKNF. But you are right (:

[Admin]

@mp0int - if you edit your answer, I can remove the downvote - it's currently locked in.

[Donal Fellows]

@snowflake: That's how these things happen, but the bad code smell remains and the data/schema should be examined for potential refactorings.

[Nicktar]

Everyone is entitled to have his own opinion. To debase someone because he shares an opinion, that's found in about each book just doesn't seem justified. I've worked on many systems and each and every one of them had tables with more than 30 columns but the smell remains. Just because it's there and in production doesn't make it right.

[TOPKAT]

I don't understand what 'bad smell' or 'poorly designed' means exept a subjective opinion.... please explain

[thomasrutter]

There is indeed some subjectivity in these terms. "Bad smell" refers to a sign in some code that your application may be poorly designed. It doesn't necessarily mean it is, but somebody else reading your code is likely to jump to that conclusion. Poorly designed means not coding something in a sensible or efficient way, using tools how they weren't intended to be used, etc. In this case it may indicate that you need to reconsider how to normalise the database design.

[Muhammad Saqib]

Right, I'm working on an ERP developed by oracle having 50+ columns in their most used table.