Philipp Lenssen Philipp Lenssen - 1 year ago 71
MySQL Question

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

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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download