Re: how table rows are stored at the file level
Almost always the discussions recommending Fixed length records in MyISAM are myths. The apparent performance improvement is swamped by the performance loss of shoveling around the wasted padding bytes.
If you have TEXT or BLOB, you have Dynamic. In most cases VARCHAR is better than CHAR, therefore you get Dynamic.
Stretching a record size (eg turning VARCHAR(50) into CHAR(50)) will usually waste a lot of disk space, lead to fewer records being cached, hence slower speed.
The only cases where I have seen Dynamic get in some trouble is in table that have a lot of churn -- UPDATEs, DELETE + INSERT, etc. When a record is DELETEd, a hole is created in the table. INSERTs go into the holes first, before extending the size of the table. A big new row might have to span more than one hole, thereby fragmenting the row, and leading to extra disk hits to read it later.
OPTIMIZE TABLE clears up fragmentation. (Don't bother doing it more than once a month.)
ALTER TABLE ... ADD COLUMN will always copy the entire table over, and rebuild all the indexes. (And, effectively, do an OPTIMIZE.) Do _not_ leave space for extra columns, it won't help.
Variable length: VARCHAR, VARBINARY; all flavors of TEXT, BLOB
Fixed length: CHAR, BINARY; all flavors of INT, DECIMAL, FLOAT, ENUM, SET, DATE, TIMESTAMP
TEXT, BLOB, and VAR% are preceded by a 1-4 byte length field. The data is the actual length, without any padding.
CHAR(100) with CHARACTER SET utf8 will occupy 300 bytes since utf8 can have up to 3 bytes per 'character'. VARCHAR(100) utf8 will occupy 2 to 302 bytes. However, 100 English characters will occupy only 102 bytes, since English characters take only 1 byte each. (The 2 is for length.)
An INDEX (in the .MYI file) is structured as a BTree in 1KB blocks. The leaf 'record' includes the value(s) of the field(s) for the row, plus a 'pointer' to the record in the data (.MYD) file. For Fixed, the pointer is a row number, for Dynamic it is a byte offset. The size of the pointer is controlled by myisam_data_pointer_size, which defaults (currently) to 6 bytes. You can change it for a table by using MAX_ROWS in CREATE TABLE or ALTER TABLE. If your table grows too big (essentially impossible) the pointer size will stop the table from growing bigger than 256 trillion rows (Fixed) or bytes (Dynamic).