Should one steer clear of adding yet another field to a larger MySQL table?

Question

I have a MySQL-InnoDB table with 350,000+ rows, containing a couple of things like id, otherId, shortTitle and so on. Now I'm in need of a Bool/ Bit field for perhaps a couple of hundreds or thousands of those rows. Should I just add that bool field into the table, or should I best create a new table referencing the IDs of the old table -- thereby not risking to cause performance issues on all the old existing functions that access the first table?

(Side info: I'm never using "SELECT * ...". The main table has lots of reading, rarely writing.)

Answer 1

What does the new column denote?

From the data modelling perspective, if the column belongs with the data under whichever normal form is in use, then put it with the data; performance impact be damned. If the column doesn't directly belong to the table, then put it in a second table with a foreign key.

Realistically, the performance impact of adding a new column on a table with ~350,000 isn't going to be particularly huge. Have you tried issuing the ALTER TABLE statement against a copy, perhaps on a local workstation?

Answer 2

Requiring a bit field for the next entries only sounds like you want to implement inheritance. If that is the case, I would add it to a new table to keep things readable. Otherwise, it doesn't matter if you add it to the main table or not, unless your queries are not using indexes, in which case I would change that first before making any other decisions regarding performance.

Answer 3

Adding a field can indeed hamper performance a little, since your table row grow larger, but it's hardly a problem for a BIT field.

Most probably, you will have exactly same row count per page, which means having no performance decrease at all.

On the other hand, using an extra JOIN to access the row value in another table will be much slower.

I'd add the column right into the table.

Answer 4

I don't know why people insist in called 350K-row tables big. In the mainframe world, that's how big the DBMS configuration tables are :-).

That said, you should be designing your tables in third normal form. If, and only if, you have performance problems, then should you consider de-normalizing.

If you have a column that will apply only to certain of the rows, it's (probably) not going to be 3NF to put it in the same table. You should have a separate table with a foreign key into your 'primary' table.

Keep in mind that's if the boolean field actually doesn't apply to some of the rows. That's a different situation to the field applying to all rows but not being known for some. In that case, a nullable column in the primary table would be better. But that doesn't sound like what you're describing.