What are the different row formats? Or when to use VARCHAR/CHAR?

MySQL dosen't have true SQL VARCHAR() types.

MySQL has instead 3 different ways to store records and uses this to emulate VARCHAR():

If one doesn't use any of the VARCHAR, BLOB or TEXT column types a fixed row size is used, otherwise a dynamic row size is used. CHAR() and VARCHAR() are treated identically from the applications point of view; Both truncates end space from the column when the column is accessed.

You can check the format used in a table with isamchk -d.

MySQL has three different table formats:

1. Fixed length tables.
   • This is the default format. It's used when there is no VARCHAR(), TEXT or BLOB column types in the table.
   • All CHAR(), NUMERIC() and DECIMAL() columns are space filled.
   • Very quick.
   • Easy to cache.
   • Easy to reconstruct after a crash as records are in fixed positions.
   • Don't have to be reorganised (with isamchk) unless a huge number of records are deleted and one wants to free space to the operating system.
   • This will usually take more disk space than dynamic tables.
2. Dynamic tables
   • Is used if there exists any columns with a VARCHAR, TEXT or BLOB type in a table.
   • All strings are dynamic (except if length < 3).
   • Each record is preceded with a bitmap for which columns are empty (") or zero (this isn't the same as null columns). Each string is saved with a length byte + string. If string is zero length or is number zero it is marked in the bit map and not saved to disk.
   • Each record is uses the exact record space required. If a record becomes larger it is split into as many pieces as required.
   • Takes usually much less disk space than fixed length records.
   • If one updates rows with information that extends the row length the row will be fragmented. In this case one may have to run isamchk -r from time to time to get better performance. Use isamchk -ei table_name for some statistics.
   • Not as easy to reconstruct because a record may be in many pieces and a link may be missing.
   • 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 an update causes an 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:
   • A read only table made with the pack_isam utility. All customers with extended MySQL email support are entitled to a copy of pack_isam for their intern usage.
   • The uncompress code exists in all MySQL distributions so even customers that doesn't have pack_isam can read tables compressed with pack_isam (on the same platform).
   • Takes very little disk space. Minimises disk usage.
   • Each record is compressed separately (very little access overhead). The header for a record is fixed 1-3 bytes depending on the biggest record in the table. Each column is compressed differently. Some of the compression types are:
     • There is usually a different Huffman table for each column.
     • Suffix space compression.
     • Prefix space compression.
     • Numbers with value 0 is stored with 1 bit.
     • If a integer is used with a smaller range the integer column is stored with the smallest possibly type. For example a LONGLONG (8 bytes) column may be stored as TINYINT column if all values are in the range 0-255.
     • If a value has only a small set of possible values, the value is converted to enum().
     • A column may use a combination of the above compressions.
   • Can handle fixed or dynamic length records, but no BLOB or TEXT columns.
   • Can be uncompressed with isamchk.

MySQL can support different index types, but the normal one is NISAM. This is a B-tree index and one can roughly calculate the size for the index file as: sum over all keys:

(key_length+4)*0.67

(This is for the worst case when all keys are inserted in sorted order.

String index are space compressed and if the first index part is a string it will also be prefix compressed. This will usually make the index file smaller if the columns are not filled up to 100% or if there are many duplicates.

Go to the first, previous, next, last section, table of contents.