MySQL dosen't have true SQL
Instead, MySQL has three different ways to store records and uses
these to emulate
If a table doesn't have any
columns, a fixed row size is used. Otherwise a dynamic row size is
VARCHAR columns are treated identically from
the application's point of view; both have trailing spaces removed
when the columns are retrieved.
You can check the format used in a table with
isamchk -d (
means ``describe the table'').
MySQL has three different table formats: fixed-length, dynamic
and compressed. These are compared below.
This is the default format. It's used when the table contains no
DECIMAL columns are space-padded
to the column width.
Easy to cache.
Easy to reconstruct after a crash, because records are located in fixed
Doesn't have to be reorganized (with
isamchk) unless a huge number of
records are deleted and you want to return free disk space to the operating
Usually requires more disk space than dynamic 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 internal usage.
The uncompress code exists in all MySQL distributions so that even
customers who don't have
pack_isam can read tables that were compressed
pack_isam (as long as the table was compressed on the same
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
There is usually a different Huffman table for each column.
Suffix space compression.
Prefix space compression.
Numbers with value
0 are stored using 1 bit.
If values in an integer column have a small range, the column is stored using
the smallest possible type. For example, a
BIGINT column (8 bytes) may
be stored as a
TINYINT column (1 byte) if all values are in the range
If a column has only a small set of possible values, the column type is
A column may use a combination of the above compressions.
Can handle fixed or dynamic length records, but not
Can be uncompressed with
MySQL can support different index types, but the normal type is
NISAM. This is a B-tree index and you can roughly calculate the size for the
index file as
(key_length+4)*0.67, summed over all keys. (This is for
the worst case when all keys are inserted in sorted order.)
String indexes are space compressed. If the first index part is a string, it
will also be prefix compressed. Space compression makes the index file
smaller if the string column has a lot of trailing space or is a
column that is not always used to the full length. Prefix compression helps
if there are many strings with an identical prefix.