How to Master dynamic table features in MySQL database

If a MyISAM table contains any variable-length columns (VARCHAR, blobs, or textdynamic), or if a table is created with the row_format=dynamic option, the dynamic storage format is used.

This format is a bit more complicated, because each row has a header that indicates how long the row is. When a record becomes longer because the result of the update is made, the record can also end at more than one location.

You can use optimize table or MYISAMCHK to defragment a table. If there are fixed-length columns in a table that you frequently access or change, and there are variable-length columns in the table, it might be a good idea to move these variable-length columns to other tables just to avoid fragmentation.

General characteristics of the Dynamic format table:

· All string columns are dynamic except for columns of less than 4 length.

· Before each record is a bitmap that indicates which column contains an empty string (for a string column) or 0 (for a numeric column). Note that this does not include columns that contain null values. If a character column is removed from the drag space after the length is zero, or if a number is listed as a value of 0, this is annotated in the bitmap and the column is not saved to disk. A non-empty string is stored as a length byte plus a string of content.

· Usually requires less disk space than a fixed-length table.

· Each record uses only the required size of space. However, if a record becomes larger, it is separated into multiple slices as needed, resulting in the recording of fragments. For example, if you update a row with information about the extended length, the line becomes fragmented. In this case, you can run optimize table or myisamchk-r occasionally to improve performance. You can use Myisamchk-ei to get statistical data for tables.

· A dynamic format table is more difficult to rebuild than a static format table after it crashes because one record can be fragmented and the link (fragmentation) may be lost.

· The expected length of the dynamic dimension record 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

Additional 6 bytes are required for each link. When an update causes a record to expand, a dynamic record is linked. Each new link is at least 20 bytes, so the next extension may be in the same link. If not, then another link will be established. You can use Myisamchk-ed to find out the number of links. All links can be removed with myisamchk-r.