Is it bad practice to have mostly unused large sized column in mysql database table?

Question

I have a MySQL database table, which have more than 100 columns. I have to add two more columns, which if entered by user, keeps text data in it, but which is hardly used.

Now my question is, what will happen if I make it as "medium text" sized column and most of the user don't enter it. Will that column still takes the given memory, or only when user enters in to it,memory will be allocated.

I dont have much knowledge in this, So any explanations are welcome. Also let me know if any other better method to go.

Answer 1

It's not bad practice to use large texts or blobs even if it's not going to be used frequently, however try to use the smallest data type that suits your needs.

• TEXT requires N characters + 2 bytes
• MEDIUMTEXT requires N characters + 3 bytes
• LONGTEXT requires N characters + 4 bytes

See: https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html

Additionally, if you allow them to be NULL (and assuming you are using InnoDB engine with COMPACT row format), it will only use 1 bit per column per row). So, if you have 2 NULLs, it will still use 1 byte.

Space Required for NULLs = CEILING(N/8) bytes where N is the number of NULL columns in a row.

More on: https://dev.mysql.com/doc/refman/5.7/en/innodb-physical-record.html

On the other hand, having that many columns might not be ideal. Try restructuring your table into several tables.

Answer 2

I think you need to split that information in three tables. One contains general info about entry, one contains fields list and other holds relation between first and second table.

[Product]
ID | name | model | price

[Fields]
ID | field_name | field_key | is_mandatory

[Field_to_product]
field_id | product_id | value

And in Field_to_product you hold only these values, that product has.

On update delete all entries for that product from Field_to_product and rewrite it's values.