FiftiN FiftiN - 1 year ago 66
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 Source

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.

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