I am trying to know on how does mysql stores the ROWs/Records on the disk, is it binary format? And how does it stores varchar fields? are those get stored separately than other fixed length data fields present in the table?
So e.g. table employee(id int, name varchar(64), salary decimal(10,2) ); And I have 1000 records in this table. How this table will be stored on disk. Will defining column name as char(64) make select queries faster on this table.
I am trying to optimize tables having lot of varchar fields.
Question is specific to InnoDB engine type.
Char(64) will use 64 Bytes per record which is 64,000 Byte for a 1000 row table. Varchar(64) will use <length of string> Bytes and 1 Byte overhead for the string length. If all your records have empty string, it will be (0+1)*1000=1,000 Bytes. If all your records have the full length of 64 letters, it will be (64+1)*1000=65,000 Bytes.
As you see the difference depends on the data you have. If you use fixed-length strings, varchar() will give you unnecessary overhead. If the length of your strings varies a lot, you may waste space with char().
Don't care about internal storage of data, the DBMS will do that itself and you cannot optimize for it as user. Your SELECT performance will be a question of finding records, this is setting good indexes rather than a problem with transfer times. Your example is a purely academic one and even for much bigger tables the transfer time will only be of importance if you do sequential retrievals of huge amounts of data.
In general, it makes sense to use char() if you have really fixed length strings and varchar() else.