As of MySQL 3.23.6, you can choose between three basic
table formats. When you create a new table, you can tell MySQL
which table type it should use for the table. MySQL will
always create a .frm
file to hold the table and column
definitions. Depending on the table type the index and data will be
stored in other files.
You can convert tables between different types with the ALTER
TABLE
statement. See section 7.8 ALTER TABLE
syntax.
MyISAM
is the default table type in MySQL 3.23. It's
based on the ISAM
code and has a lot of useful extensions.
The index is stored in a file with the .MYI
(MYindex) extension
and the data is stored in file with the .MYD
(MYData) extension.
You can check/repair MyISAM
tables with the myisamchk
utility. See section 14.4 Using myisamchk
for crash recovery.
The following is new in MyISAM
:
INSERT
new rows in a table without deleted rows,
while other threads are reading from the table.
AUTO_INCREMENT
column. MyISAM
will automatically update this on INSERT/UPDATE
. The
AUTO_INCREMENT
value can be reset with myisamchk
. This
will make AUTO_INCREMENT
columns faster and old numbers will not
be reused as with the old ISAM. Note that when a AUTO_INCREMENT
is defined on the end of a multi-part-key the old behavior is still present.
BLOB
and TEXT
columns can be indexed.
NULL
values are allowed in indexed columns. This takes 0-1
bytes/key.
myisamchk
.
MyISAM
file that indicates whether or not the
table was closed correctly. This will soon be used for automatic repair
in the MySQL server.
myisamchk
will now mark tables as checked. myisamchk
--fast
will only check those tables that don't have this mark.
myisamchk -a
stores statistics for key parts (and not only for
whole keys as in ISAM
).
myisampack
can pack BLOB
and VARCHAR
columns.
MyISAM
also supports the following things, which MySQL
will be able to use in the near future.
VARCHAR
type; A VARCHAR
column starts
with a length stored in 2 bytes.
VARCHAR
may have fixed or dynamic record length.
VARCHAR
and CHAR
may be up to 64K.
All key segments have their own language definition. This will enable
MySQL to have different language definitions per column.
UNIQUE
; This will allow
you to have UNIQUE
on any combination of columns in a table. (You
can't search on a UNIQUE
computed index, however.)
MySQL can support different index types, but the normal type is
ISAM or MyISAM. These uses 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 and we don't have any compressed keys.).
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 than the above figures if the string column has a lot
of trailing space or is a VARCHAR
column that is not always used
to the full length. Prefix compression is used on keys that start
with a string. Prefix compression helps if there are many strings
with an identical prefix.
In MyISAM
tables, you can also prefix compress numbers by specifying
PACK_KEYS=1
when you create the table. This helps when you have
many integer keys which have an identical prefix when the numbers are stored
high-byte first.
MyISAM supports 3 different table types. 2 of them are chosen
automatically depending on the type of columns you are using. The third,
compressed tables, can only be created with the myisampack
tool.
This is the default format. It's used when the table contains no
VARCHAR
, BLOB
or TEXT
columns.
This format is the simplest and most secure format. It is also the fastest of the on-disk formats. The speed comes from the easy way data can be found on disk. When looking up something with a index and static format it very simple, just multiply the row number with the row length.
Also when scanning a table it is very easy to read a constant number of records with each disk read.
The security comes from if your computer crashes when writing to a
static MyISAM file, myisamchk
can easily figure out where each
row starts and ends. So it can usually reclaim all records except the
partially written one. Note that in MySQL all indexes can always be
reconstructed.
CHAR
, NUMERIC
and DECIMAL
columns are space-padded
to the column width.
myisamchk
) unless a huge number of
records are deleted and you want to return free disk space to the operating
system.
This format is used if the table contains any VARCHAR
, BLOB
or TEXT
columns or if the table was created with
ROW_FORMAT=dynamic
.
This format is a litte more complex since each row has to have a header that says how long it is. One record can also end up at more than one location when it is made longer at an update.
You can use OPTIMIZE table
or myisamchk
to defragment a
table. If you have static data that you acess/change a lot in the same
table as some VARCHAR
or BLOB
columns, it might be a good
idea to move the dynamic columns to other tables just to avoid
fragmentation.
''
) for string columns, or zero for numeric columns (this isn't
the same as columns containing NULL
values). If a string column
has a length of zero after removal of trailing spaces, or a numeric
column has a value of zero, it is marked in the bit map and not saved to
disk. Non-empty strings are saved as a length byte plus the string
contents.
myisamchk
-r
from time to time to get better performance. Use myisamchk -ei
tbl_name
for some statistics.
3 + (number of columns + 7) / 8 + (number of char columns) + packed size of numeric columns + length of strings + (number of NULL columns + 7) / 8There is a penalty of 6 bytes for each link. A dynamic record is 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
myisamchk -ed
. All links may be removed with myisamchk -r
.
This is a read only type that is generated with the optional
myisampack
tool (pack_isam
for ISAM
tables).
myisampack
and pack_isam
are available to all customers
that have bought a MySQL license or MySQL
support
for their internal use.
myisampack
can read tables that
were compressed with myisampack
0
are stored using 1 bit.
BIGINT
column (8 bytes) may
be stored as a TINYINT
column (1 byte) if all values are in the range
0
to 255
.
ENUM
.
BLOB
or TEXT
columns.
myisamchk
.
You can also use the deprecated ISAM table type. This will disappear
rather soon since MyISAM
is a better implementation of the same
thing. ISAM uses a B-tree
index. The index is stored in a file
with the .ISM
extension and the data is stored in file with the
.ISD
extension. You can check/repair ISAM tables with the
isamchk
utility. See section 14.4 Using myisamchk
for crash recovery.
ISAM
has the following features/properties:
Most of the things for MyISAM
tables are also true for ISAM
tables. See section 8.1 MyISAM tables. The major differences compared to MyISAM
tables are:
pack_isam
rather than with myisampack
.
HEAP
tables use a hashed index and are stored in memory. This
makes them very fast, but if MySQL crashes you will lose all
data stored in them. HEAP
is very useful for temporary tables!
The MySQL internal HEAP tables uses 100% dynamic hashing
without overflow areas. There is no extra space needed for free lists.
HEAP
tables also don't have problems with delete + inserts, which
normally is common with hashed tables..
mysql> CREATE TABLE test TYPE=HEAP SELECT ip,SUM(downloads) as down FROM log_table GROUP BY ip; mysql> SELECT COUNT(ip),AVG(down) FROM test; mysql> DROP TABLE test;
Here are some things you should consider when you use HEAP
tables:
MAX_ROWS
in the CREATE
statement
to ensure that you accidently do not use all memory.
=
and <=>
(but are VERY fast).
HEAP
tables can only use whole keys to search for a row; compare this
to MyISAM
tables where any prefix of the key can be used to find rows.
HEAP
tables use a fixed record length format.
HEAP
doesn't support BLOB
/TEXT
columns.
HEAP
doesn't support AUTO_INCREMENT
columns.
HEAP
doesn't support an index on a NULL
column.
HEAP
table (this isn't common for
hashed tables).
HEAP
tables are shared between all clients (just like any other
table).
ORDER BY
).
HEAP
tables are allocated in small blocks. The tables
are 100% dynamic (on inserting). No overflow areas and no extra key
space is needed. Deleted rows are put in a linked list and are
reused when you insert new data into the table.
DELETE FROM heap_table
or
DROP TABLE heap_table
.
MyISAM
table to a HEAP
table.
HEAP
tables bigger than max_heap_table_size
.
Memory needed for one row in a HEAP
table is:
SUM_OVER_ALL_KEYS(max_length_of_key + sizeof(char*)*2) + ALIGN(length_of_row+1,sizeof(char*))
sizeof(char*)
is 4 on 32 bit machines and 8 on 64 bit machines.
Go to the first, previous, next, last section, table of contents.