MySQL - How Many Columns Is Too Many

mysql - how many columns is too many?

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.

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

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.

How many columns is too many columns?

The design of the table depends on the entity it needs to store. If all the data belongs together, then 50 columns (or even 100) might be the correct thing to do.

So long as the table is normalized, there is no rule of thumb regarding size, apart from database capabilities and the need to optimize.

How many database table columns are too many?

Do not go the key / value route. SQL isn't designed to handle it and it'll make getting actual data out of your database an exercise in self torture. (Examples: Indexes don't work well. Joins are lots of fun when you have to join just to get the data you're joining on. It goes on.)

As long as the data is normalized to a decent level you don't have too many columns.

EDIT: To be clear, there are some problems that can only be solved with the key / value route. "Too many columns" isn't one of them.

mysql too many columns?

To elaborate on RichardOD's answer, you generally have three options when dealing with subtyping, and which you choose depends on what you need to do with the data in question.

The first option is the one you're currently using: keep all columns related to the different types in one table, with flags and nulls used to indicate which type a given record is. It is the simplest way to manage subtyping, and it generally works well when you only have a few types or if the different types aren't very different. In your case, it seems like the types can vary quite a bit.

The second option is to keep a central table that contain all of the common columns between the subtypes, and have one-to-one relationships with other tables that contains the type-specific details of those types.

The third option is to not think of the different types as subtypes at all and just keep all the types' records in separate tables. So you'd have no common table between the types that keeps the common data, and each table would have some columns that are repeated across tables.

Now, each option has its place. You'd use the first option when there aren't many differences between the different types. You'd use the second option if you need to manipulate the common fields independently of the type-specific fields; for example, if you wanted to list all sports games in a big grid with general information, and then let users click to see the type-specific details of that game. You'd use the third option when the types aren't really very related at all and you're just storing them together out of convenience; dissimilar schemas, even if it shares a few fields, shouldn't be merged.

So think about what you need to do with the data and how it fits into the three options and decide for yourself which is best. If you can't decide, update your question with the details about how you plan to use the data and I or someone else should be able to help you more.

Having many rows vs having many columns: which is the way to go?

Second one is definitely the way to go.

You always want to keep in mind that you want to be able to expand your system. If a new subject is introduced in 5 years. You want to be able to add that. And with that you don't want to add an extra field and leave that empty for all the records you already have.

It's also better to keep everything seperated. There will probably be alot of times where you won't need 90% of that data. It's better to just get it all from 1 marks table then.

So I'd say go for option 2. Make a marks table with student_regno, subject_id, mark

---

Related Topics