Dynamic format is a little more complex than static format because each row has a header that indicates how long it is. A row can become fragmented (stored in noncontiguous pieces) when it is made longer as a result of an update.
You can use
OPTIMIZE TABLE or
myisamchk -r to defragment a table. If you
have fixed-length columns that you access or change frequently
in a table that also contains some variable-length columns, it
might be a good idea to move the variable-length columns to
other tables just to avoid fragmentation.
Dynamic-format tables have these characteristics:
All string columns are dynamic except those with a length less than four.
Each row is preceded by a bitmap that indicates which
columns contain the empty string (for string columns) or
zero (for numeric columns). Note that this does not
include columns that contain
values. If a string column has a length of zero after
trailing space removal, or a numeric column has a value of
zero, it is marked in the bitmap and not saved to disk.
Nonempty strings are saved as a length byte plus the
Much less disk space usually is required than for fixed-length tables.
Each row uses only as much space as is required. However,
if a row becomes larger, it is split into as many pieces
as are required, resulting in row fragmentation. For
example, if you update a row with information that extends
the row length, the row becomes fragmented. In this case,
you may have to run
TABLE or myisamchk -r from
time to time to improve performance. Use
myisamchk -ei to obtain table
More difficult than static-format tables to reconstruct after a crash, because rows may be fragmented into many pieces and links (fragments) may be missing.
The expected row length for dynamic-sized rows is calculated using the following expression:
3 + (
number of columns+ 7) / 8 + (
number of char columns) + (
packed size of numeric columns) + (
length of strings) + (
number of NULL columns+ 7) / 8
There is a penalty of 6 bytes for each link. A dynamic row
is linked whenever an update causes an enlargement of the
row. Each new link is at least 20 bytes, so the next
enlargement probably goes in the same link. If not,
another link is created. You can find the number of links
using myisamchk -ed. All links may be
or myisamchk -r.