OT: SQL Null fields

[Marc Vanzegbroeck]

Hi,

I know it's not really a harbour/Fivewin question but now I have table with appointment-data with a couple of fields.
Some appointments need extra info (50 fields extra).
Only about 5% need this extra info. To save disc-space I have created a second table with those fields and one with the appointment-id, to link with the main table.

Now I'am converting this to MySQL and I was wondering if this is still necessary.
If I only create one table with all the info. Are the empty fields (null) still consuming space on the hard-disk in MySQL?

I know that a DBF-file use the same disk-space for an empty field as one with data, but does MySQL do this also?

Thanks,
Marc

[Kleyber]

Marc,

In MySQL you can use the VARCHAR type, in order to consume less space in the database, once this type of field increases only when receive data. If you use CHAR type the space you have defined in the field remains the same all the time. In this case I suggest you to use a second table, as you said and there is no problem (my opinion).

[Adolfo]

Marc.

Using TEXT fields could be a solution. You'll know that each field will use at least 10 BYTES, but you can put whatever you can within these fields.
You can try first with TINYTEXT, (up to 255 char length max) or TEXT ( here you have 2^24 characters)

Remember that using TEXT columns, (if you modify them constantly) you can produce table fragmentation, whic can be overcome with an OPTIMIZE TABLE command once in a while.

From Chile
Adolfo

[Marc Vanzegbroeck]

Kleyber, Adolfo,

Thank you for the information.
Most of the fields are CHAR. So I think I will continue to use my second table.
It's less work to convert my program , but I was just wondering how MySQL use his space, so I could maybe use in the future only one instead 2 tables if have the same situation...

Regards,
Marc