Databases Reference
In-Depth Information
tables aren't large and won't be painful to repair, it isn't out of the question to use it.
(But please, don't use it by default. Use InnoDB instead.)
Storage
MyISAM typically stores each table in two files: a data file and an index file. The two
files bear
.MYD
and
.MYI
extensions, respectively. MyISAM tables can contain either
dynamic or static (fixed-length) rows. MySQL decides which format to use based on
the table definition. The number of rows a MyISAM table can hold is limited primarily
by the available disk space on your database server and the largest file your operating
system will let you create.
MyISAM tables created in MySQL 5.0 with variable-length rows are configured by
default to handle 256 TB of data, using 6-byte pointers to the data records. Earlier
MySQL versions defaulted to 4-byte pointers, for up to 4 GB of data. All MySQL ver-
sions can handle a pointer size of up to 8 bytes. To change the pointer size on a MyISAM
table (either up or down), you must alter the table with new values for the
MAX_ROWS
and
AVG_ROW_LENGTH
options that represent ballpark figures for the amount of space you
need. This will cause the entire table and all of its indexes to be rewritten, which might
take a long time.
MyISAM features
As one of the oldest storage engines included in MySQL, MyISAM has many features
that have been developed over years of use to fill niche needs:
Locking and concurrency
MyISAM locks entire tables, not rows. Readers obtain shared (read) locks on all
tables they need to read. Writers obtain exclusive (write) locks. However, you can
insert new rows into the table while select queries are running against it (concurrent
inserts).
Repair
MySQL supports manual and automatic checking and repairing of MyISAM tables,
but don't confuse this with transactions or crash recovery. After repairing a table,
you'll likely find that some data is simply gone. Repairing is slow, too. You can use
the
CHECK TABLE mytable
and
REPAIR TABLE mytable
commands to check a table for
errors and repair them. You can also use the
myisamchk
command-line tool to
check and repair tables when the server is offline.
Index features
You can create indexes on the first 500 characters of
BLOB
and
TEXT
columns in
MyISAM tables. MyISAM supports full-text indexes, which index individual words
for complex search operations. For more information on indexing, see
Chapter 5
.
