mysql - how many columns is too many?

I'm setting up a table that might have upwards of 70 columns. I'm now thinking about splitting it up as some of the data in the columns won't be needed every time the table is accessed. Then again, if I do this I'm left with having to use joins.

At what point, if any, is it considered too many columns?

Solution 1:

It's considered too many once it's above the maximum limit supported by the database.

The fact that you don't need every column to be returned by every query is perfectly normal; that's why SELECT statement lets you explicitly name the columns you need.

As a general rule, your table structure should reflect your domain model; if you really do have 70 (100, what have you) attributes that belong to the same entity there's no reason to separate them into multiple tables.

Solution 2:

There are some benefits to splitting up the table into several with fewer columns, which is also called Vertical Partitioning. Here are a few:

1. If you have tables with many rows, modifying the indexes can take a very long time, as MySQL needs to rebuild all of the indexes in the table. Having the indexes split over several table could make that faster.

2. Depending on your queries and column types, MySQL could be writing temporary tables (used in more complex select queries) to disk. This is bad, as disk i/o can be a big bottle-neck. This occurs if you have binary data (text or blob) in the query.

3. Wider table can lead to slower query performance.

Don't prematurely optimize, but in some cases, you can get improvements from narrower tables.

Solution 3:

It is too many when it violates the rules of normalization. It is pretty hard to get that many columns if you are normalizing your database. Design your database to model the problem, not around any artificial rules or ideas about optimizing for a specific db platform.

Apply the following rules to the wide table and you will likely have far fewer columns in a single table.

1. No repeating elements or groups of elements
2. No partial dependencies on a concatenated key
3. No dependencies on non-key attributes

Here is a link to help you along.