UNDERSTANDING MYSQL’S STORAGE ENGINES
Author : Suraj Jaiswal
MyISAM is MySQL’s default table storage engine. It extends the base ISAM (Indexed Sequential Access Method) type with a number of additional optimizations and enhancements.
It provides a large list of features, such as full-test indexing, compression, and spatial (GIS) functions. It doesn’t support transactions or row-level locks. The biggest weakness is undoubtedly the fact that it isn’t, even remotely, crash-safe.
Each MyISAM table is stored on disk in 3 files. The files have names that begin with the table name and have an extension to indicate the file type.
1) .frm file stores the table definition.
2) The data file has an .MYD(MYData) extension.
3) The indexfile has an .MYI(MyIndex) extension.
Since tables cannot be distributed across multiple .MYD files, each table is limited to the maximum size of a file in the operating system.
As one of the oldest storage engines included in MySQL, MyISAM has many features:
– MyISAM tables are optimized for compression and speed.
– MyISAM is optimized for environments with heavy read operations, and few writes, or none at all.
– Support large table files (up to 256TB in size).
– Allow indexing of BLOB and TEXT columns. 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.
– Because of its compact data storage and low overhead due to its simpler design, MyISAM can provide good performance for some uses.
However, MyISAM simplicity has several drawbacks. The major deficiency of MyISAM is the absence of transactions support. Also, foreign keys are not supported. And, MyISAM has poor crash recovery.