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

Question

Is there some performance loss if one of tables in my database has huge amount of columns? Let's say I have a table with 30 columns.

Should I consider splitting the table into few smaller ones or is it ok?

What is a recommended maximum amount of columns per database table?

Thank you.

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

Beyond performance, DataBase normalization is a need for databases with too many tables and relations. Normalization gives you easy access to your models and flexible relations to execute diffrent sql queries.

As it is shown in here, there are eight forms of normalization. But for many systems, applying first, second and third normal forms is enough.

So, instead of selecting related columns and write long sql queries, a good normalized database tables would be better.

Answer 5

30 columns would not normally be considered an excessive number.

Three thousand columns, on the other hand... http://stackoverflow.com/questions/3400956/how-would-you-implement-a-very-wide-table

Answer 6

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 inthe documentation about how the datbase 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 is not unusually big and will probably be OK.