All data values are stored with the low byte first. This makes
the data machine and operating system independent. The only
requirements for binary portability are that the machine uses
two's-complement signed integers and IEEE floating-point
format. These requirements are widely used among mainstream
machines. Binary compatibility might not be applicable to
embedded systems, which sometimes have peculiar processors.
There is no significant speed penalty for storing data low
byte first; the bytes in a table row normally are unaligned
and it takes little more processing to read an unaligned byte
in order than in reverse order. Also, the code in the server
that fetches column values is not time critical compared to
All numeric key values are stored with the high byte first to
allow better index compression.
Large files (up to 63-bit file length) are supported on
filesystems and operating systems that support large files.
The maximum number of indexes per
table is 64. This can be changed by recompiling. Beginning
with MySQL 5.1.4, you can configure the build by invoking
configure with the
N is the maximum
number of indexes to permit per
N must be less thann or
equal to 128. Before MySQL 5.1.4, you must change the source.
The maximum number of columns per index is 16.
The maximum key length is 1000 bytes. This can also be changed
by changing the source and recompiling. For the case of a key
longer than 250 bytes, a larger key block size than the
default of 1024 bytes is used.
When rows are inserted in sorted order (as when you are using
AUTO_INCREMENT column), the index tree
is split so that the high node only contains one key. This
improves space utilization in the index tree.
Internal handling of one
column per table is supported.
automatically updates this column for
operations. This makes
columns faster (at least 10%). Values at the top of the
sequence are not reused after being deleted. (When an
AUTO_INCREMENT column is defined as the
last column of a multiple-column index, reuse of values
deleted from the top of a sequence does occur.) The
AUTO_INCREMENT value can be reset with
ALTER TABLE or
Dynamic-sized rows are much less fragmented when mixing
deletes with updates and inserts. This is done by
automatically combining adjacent deleted blocks and by
extending blocks if the next block is deleted.
If a table has no free blocks in the middle of the data file,
INSERT new rows into it at the same
time that other threads are reading from the table. (These are
known as concurrent inserts.) A free block can occur as a
result of deleting rows or an update of a dynamic length row
with more data than its current contents. When all free blocks
are used up (filled in), future inserts become concurrent
again. See Section 7.3.3, “Concurrent Inserts”.
You can put the data file and index file on different
directories to get more speed with the
table options to
CREATE TABLE. See
Section 13.1.5, “
CREATE TABLE Syntax”.
can be indexed.
NULL values are allowed in indexed columns.
This takes 0–1 bytes per key.
Each character column can have a different character set. See
Chapter 10, Character Set Support.
There is a flag in the
MyISAM index file
that indicates whether the table was closed correctly. If
mysqld is started with the
MyISAM tables are automatically checked
when opened, and are repaired if the table wasn't closed
myisamchk marks tables as checked if you
run it with the
myisamchk --fast checks only those tables
that don't have this mark.
myisamchk --analyze stores statistics for
portions of keys, as well as for entire keys.
myisampack can pack