FiftiN FiftiN - 5 months ago 10
MySQL Question

What is the cost of an additional column

I have table

fields
with only 3 columns:
owner_id INT, value VARCHAR(255), type INT
. It is planned that this table will store a lot of data.

I need to add column to store values with big length (TEXT). I see 2 ways.


  • Add new column to this table with type TEXT.

  • Add new table like this:
    CREATE TABLE field_contents(field_id INT, content TEXT)
    .



What a better way? What is the cost (drive space, memory, CPU) of an additional TEXT-column if in 99% it will be empty?

Answer

I don't see anything wrong with adding the content column to the original fields table, at least from a storage point of view. NULL values in variable length columns in MySQL do not take up any space in the row themselves. Since TEXT is a BLOB type, and has a variable length, the empty entries in your table should therefore not be a performance killer for storage.

As to whether the content column logically belongs in the fields table itself depends on your database design.

Read here for more information.