5 Jul 07
Actually using no VARCHAR or BLOB types results in a fixed row size. Otherwise CHAR and VARCHAR are the same. Se the below for a more exact definition.
You can check the format used for a table with isamchk -d. <http://greateventsupport.com/freehand/freehand-lessons/index.html>
MySQL has three different table formats:
1. Fixed length tables;
o The default format.
o All non packed columns are space filled
o Very quick.
o Easy to cash
o Easy to reconstruct if crashed (Of course this only theoretical :-) because records are on fixed positions.
o Don't have to be reorganized unless a huge number of records are deleted.
2. Dynamic tables
o Is used if there exists any VARCHAR or BLOB columns in table.
o All strings are dynamic (except if length < 3).
o Each record is preceded with a bitmap for which columns are not empty (this isn't the same as null columns).
o Each string is saved with a length byte + string. If string is zero length or a number is zero it takes no extra place (just the zero length bit for each column).
o Each record is uses exactly the needed record space. If a record gets larger it's split into as many pieces as needed.
o Takes little disk space.
o If records are changed a lot, isamchk -r should be run now and then to reorganize the table. This is to get a better layout. Use isamchk -ei table_name for some statistics.
o Not as easy to reconstruct because a record may be in many pieces and a link may be missing.
o The expected row length for dynamic sized records is: 3 + (number_of_columns + 7) / 8 + (number of char columns) + packed_size_of_number_columns + length_of_strings + (null_columns + 7) / 8. There will be a penalty of 6 bytes for each link. A dynamic record will be linked whenever a update causes a enlargement of the record. Each new link will be at least 20 bytes, so the next enlargement will probably go in the same link. If not there will be another link. You may check how many links there are with isamchk -ed. All links may be removed with isamchk -r.
3. Compressed tables (this is only with UNIREG/pack_isam)
o Read only tables.
o Takes very little disk space. Minimizes disk usage.
o Each record is compressed separately (very little access overhead)
o Can handle fixed or dynamic length records (but no BLOB:s).
o Can be uncompressed with isamchk