Mysql – thesql separate email and cellphone from user table to avoid extra unused-space usage

MySQLperformance

i have a question about putting some string (fixed-length and variable (which Might be always null)) into a table or separate them (Because of space-usage even if it's null) …

for example, my user register with cellphone number or an email address

this is for cellphone number:

   cellphone_country_code (SMALLINT) (UNSIGNED)
   cellphone_num          (BIGINT)   (UNSIGNED)

and this is for email address:

   email_address         (CHAR(60))

Now which options are better ? (i want to know that i have to separate these data from user table or not)

option 1

user:

     id                      .....
     cellphone_country_code  (SMALLINT) (UNSIGNED)    (Default = 0)
     cellphone_num           (BIGINT)   (UNSIGNED)    (Default = 0)
     email_address           (CHAR(60)) (NULL)        (Default = NULL)

or (option 2)

user:
         id                      .....
         ....

user_cellphone:

         user_id                ......      (unique-index)
         cellphone_country_code  (SMALLINT) (UNSIGNED)
         cellphone_num           (BIGINT)   (UNSIGNED)

user_email:

         user_id                ......     (unique-index)
         email_address          (CHAR(60))

for option 1, if user registers with cellphone number and never want to add an email address, we have an empty-unused 60-BYTE space for email address (which we not going to use it for that user !)
so i think it's bad to have a column with extra bytes that you are not going to use !!! but it takes your ram space !

but in option 2, there is no extra unused-space because we add cellphone number or email address in separated tables so for example, if email address is not provided, there is no any empty-unused 60-BYTE space !!!!!

am i right ? what do you think ? which option is better ?
if we have a column (string with fixed-length or variable-length) and we don't use it, still it's get ram space (based on it's length i mean) ???

Best Answer

Use InnoDB, not MyISAM.

Use CHAR only for columns that are truly fixed length (country_code, uuid, postal_code, md5, etc). email is not likely to be such.

I recommend against using a numeric field for a phone number. What if the user has an extension? Or needs a leading 0? Or enters dashes, etc?

How many users are you expecting? A million translates to well under 1GB. Even a billion users, with tens of GB needed is probably not important compared to what else is going on in your multi-GB server.

Use NULL when the business logic needs. For phone and email in your app, it means "not supplied". That's fine.

If you dissect the table structure, you will find that more than half of the bytes are "overhead" are "free". Your attempted optimization is relatively insignificant.

There are other reasons for splitting columns out from the table, but I don't think you have found a valid one.

Related Question