Explore the MyISAM storage engine in MariaDB Server. Understand its characteristics, including suitability for read-heavy workloads, and its role in specific use cases.
This page explains how MyISAM stores and compresses indexes, detailing space usage for different key types and compression strategies.
Regular MyISAM tables make use of B-tree indexes.
String indexes are space-compressed, which reduces the size of VARCHARs that don't use the full length, or a string that has trailing spaces. String indexes also make use of prefix-compression, where strings with identical prefixes are compressed.
Numeric indexes can also be prefix-compressed compressed if the PACK_KEYS=1 option is used. Regardless, the high byte is always stored first, which allows a reduced index size.
In the worst case, with no strings being space-compressed, the total index storage space are (index_length+4)/0.67 per index.
This page is licensed: CC BY-SA / Gnu FDL
A detailed overview of the MyISAM storage engine, including its file structure, features, and limitations compared to newer engines like Aria.
The MyISAM storage engine was the default storage engine from MySQL 3.23 until it was replaced as default by InnoDB in MariaDB and MySQL 5.5. Historically, MyISAM is a replacement for the older ISAM engine, removed in MySQL 4.1.
It's a light, non-transactional engine with great performance, is easy to copy between systems and has a small data footprint.
You're encouraged to rather use the Aria storage engine for new applications, which has even better performance in most cases and the goal of being crash-safe.
A MyISAM table is stored in three files on disk. There's a table definition file with an extension of .frm, a data file with the extension .MYD, and an index file with the extension .MYI.
Does not support .
Does not support foreign keys.
Supports .
Supports data types.
This page is licensed: CC BY-SA / Gnu FDL
Storage limit of 256TB.
Maximum of 64 indexes per table.
Maximum of 32 columns per index.
Maximum index length of 1000 bytes.
Limit of (232)2 (1.844E+19) rows per table.
Supports large files up to 63-bits in length where the underlying system supports this.
All data is stored with the low byte first, so all files will still work if copied to other systems or other machines.
The data file and the index file can be placed on different devices to improve speed.
Supports table locking, not row locking.
Supports a key buffer that is segmented in MariaDB.
Supports concurrent inserts.
Supports fixed length, dynamic and compressed formats - see MyISAM Storage Formats.
Numeric index values are stored with the high byte first, which enables more efficient index compression.
Data values are stored with the low byte first, making it mostly machine and operating system independent. The only exceptions are if a machine doesn't use two's-complement signed integers and the IEEE floating-point format.
Can be copied between databases or systems with normal system tools, as long as the files are not open on either system. Use FLUSH_TABLES to ensure files are not in use.
There are a number of tools for working with MyISAM tables. These include:
mariadb-check for checking or repairing
myisamchk for checking or repairing
myisampack for compressing
It is possible to build a MERGE table on the top of one or more MyISAM tables.
Learn about the three storage formats supported by MyISAM: FIXED (static), DYNAMIC (variable length), and COMPRESSED (read-only).
The storage engine supports three different table storage formats.
These are FIXED, DYNAMIC and COMPRESSED. FIXED and DYNAMIC can be set with the ROW FORMAT option in the statement, or are chosen automatically depending on the columns the table contains. COMPRESSED can only be set via the tool.
The statement can be used to see the storage format used by a table. Note that COMPRESSED tables are reported as DYNAMIC in that context.
Fixed-length (or static) tables contain records of a fixed-length. Each column is the same length for all records, regardless of the actual contents. It is the default format if a table has no BLOB, TEXT, VARCHAR or VARBINARY fields, and no ROW FORMAT is provided. You can also specify a fixed table with ROW_FORMAT=FIXED in the table definition.
Tables containing BLOB or TEXT fields cannot be FIXED, as by design these are both dynamic fields. However, no error or warning are raised if you specify FIXED.
Fixed-length tables have a number of characteristics
fast, since MariaDB will always know where a record begins
easy to repair: myisamchk is always able to recover all rows, except for the last one if it is not entirely written
easy to cache
take up more space than dynamic or compressed tables, as the maximum amount of storage space are allocated to each record.
reconstructing after a crash is uncomplicated due to the fixed positions
no fragmentation or need to re-organize, unless records have been deleted and you want to free the space up.
Dynamic tables contain records of a variable length. It is the default format if a table has any BLOB, TEXT, VARCHAR or VARBINARY fields, and no ROW FORMAT is provided. You can also specify a DYNAMIC table with ROW_FORMAT=DYNAMIC in the table definition. If the table contains BLOB or TEXT columns, its format is always DYNAMIC, and the ROW FORMAT option is ignored.
Dynamic tables have a number of characteristics
Each row contains a header indicating the length of the row.
Rows tend to become fragmented easily. UPDATING a record to be longer will likely ensure it is stored in different places on the disk. Use OPTIMIZE TABLE when the fragmentation is too high.
All string columns with a length of four or more are dynamic.
They require much less space than fixed-length tables.
Restoring after a crash is more complicated than with FIXED tables. Some fragments may be lost.
If a DYNAMIC table has some frequently-accessed fixed-length columns, it could be a good idea to move them into a separate FIXED table to avoid fragmentation.
Compressed tables are a read-only format, created with the myisampack tool. This can be done while the server is running, but external lock must not be disabled. myisamchk is used to uncompress them.
Compressed tables have a number of characteristics:
while the data is read-only, DDL statements such as DROP TABLE and TRUNCATE TABLE will still function.
take much less space than fixed or dynamic tables. Each data has usually a 40-70% compression ratio
rows are compressed separately, reducing access overhead.
row headers are from one to three bytes.
rows can be compressed with different compression types, including
prefix space compression
suffix space compression
columns with small sets of values are converted to ENUM
numeric zeros are stored with only one bit
Why we still need MyISAM (for read-only tables) describes an important use case for MyISAM compressed tables.
This page is licensed: CC BY-SA / Gnu FDL
integer columns are reduced to the smallest int type that can hold the contents
A reference for system variables that configure MyISAM behavior, such as key cache sizes, recovery modes, and concurrent insert settings.
This page documents system variables related to the MyISAM storage engine. For options, see MyISAM Options.
See Server System Variables for a complete list of system variables and instructions on setting them.
See also the Full list of MariaDB options, system and status variables.
key_buffer_sizeDescription: Size of the buffer for the index blocks used by MyISAM tables and shared for all threads. See for more on selecting the best value.
Command line: --key-buffer-size=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 134217728
Range: 8 upwards (upper limit determined by operating system per process limit)
key_cache_age_thresholdDescription: The lower the setting, the more quickly buffers move from the hot key cache sublist to the warm sublist.
Command line: --key-cache-age-threshold=#
Scope: Global
Dynamic: Yes
key_cache_block_sizeDescription: key cache block size in bytes .
Command line: --key-cache-block-size=#
Scope: Global
Dynamic: Yes
key_cache_division_limitDescription: Percentage to use for the warm key cache buffer list (the remainder is allocated between the hot and cold caches).
Command line: --key-cache-division-limit=#
Scope: Global
Dynamic: Yes
key_cache_file_hash_sizeDescription: Number of hash buckets for open and changed files. If you have many MyISAM files open you should increase this for faster flushing of changes. A good value is probably 1/10th of the number of possible open MyISAM files.
Command line: --key-cache-file-hash-size=#
Scope: Global
Dynamic: Yes
key_cache_segmentsDescription: The number of segments in a key cache. See .
Command line: --key-cache-segments=#
Scope: Global
Dynamic: Yes
myisam_block_sizeDescription: Block size to be used for MyISAM index pages.
Command line: --myisam-block-size=#
Scope: Global
Dynamic: No
myisam_data_pointer_sizeDescription: Size in bytes of the default pointer, used in a with no MAX_ROWS option.
Command line: --myisam-data-pointer-size=#
Scope: Global
Dynamic: Yes
myisam_max_extra_sort_file_sizeDescription: Removed in MySQL 5.0.6, was used as a way to force long character keys in large tables to use the key cache method.
Removed: MySQL 5.0.6
myisam_max_sort_file_sizeDescription: Maximum size in bytes of the temporary file used while recreating a MyISAM index. If the this size is exceeded, the slower process of using the key cache is done instead.
Command line: --myisam-max-sort-file-size=#
Scope: Global
Dynamic: Yes
myisam_mmap_sizeDescription: Maximum memory in bytes that can be used for memory mapping compressed MyISAM files. Too high a value may result in swapping if there are many compressed MyISAM tables.
Command line: --myisam-mmap-size=#
Scope: Global, Session
Dynamic: Yes
myisam_recover_optionsDescription: MyISAM recovery mode. Multiple options can be selected, comma-delimited. Using no argument is equivalent to specifying DEFAULT, while specifying "" is equivalent to OFF. If enabled each time the server opens a MyISAM table, it checks whether it has been marked as crashed, or wasn't closed properly. If so, mysqld will run a check and then attempt to repair the table, writing to the error log beforehand.
OFF: No recovery.
BACKUP: If the data file is changed while recovering, saves a backup of the .MYD data file. t.MYD are saved as t.MYD-datetime.BAK.
myisam_repair_threadsDescription: If set to more than 1, the default, MyISAM table indexes each have their own thread during repair and sorting. Increasing from the default will usually result in faster repair, but will use more CPU and memory.
Command line: --myisam-repair-threads=#
Scope: Global, Session
myisam_sort_buffer_sizeDescription: Size in bytes of the buffer allocated when creating or sorting indexes on a MyISAM table. Increase for better performance.
Command line: --myisam-sort-buffer-size=#
Scope: Global, Session
Dynamic: Yes
myisam_stats_methodDescription: Determines how NULLs are treated for the purposes of index statistics. If set to nulls_equal, all NULL index values are treated as a single group. This is usually fine, but if you have large numbers of NULLs the average group size is slanted higher, and the optimizer may miss using the index for ref accesses when it would be useful. If set to nulls_unequal, the default, the opposite approach is taken, with each NULL forming its own group of one. Conversely, the average group size is slanted lower, and the optimizer may use the index for ref accesses when not suitable. Setting to nulls_ignored ignores NULLs altogether from index group calculations. See also , , .
Command line: --myisam-stats-method=name
myisam_use_mmapDescription: If set to 1 (0 is default), memory mapping are used to reading and writing MyISAM tables.
Command line: --myisam-use-mmap
Scope: Global
Dynamic: Yes
This page is licensed: CC BY-SA / Gnu FDL
Data Type: numeric
Default Value: 300
Range: 100 to 4294967295
Data Type: numeric
Default Value: 1024
Range: 512 to 16384
Data Type: numeric
Default Value: 100
Range: 1 to 100
Data Type: numeric
Default Value: 512
Range: 128 to 16384
Type: numeric
Default Value: 0 (non-segmented)
Range: 0 to 64
Data Type: numeric
Default Value: 1024
Data Type: numeric
Default Value: 6
Range: 2 to 7
Data Type: numeric
Default Value - 32 bit: 2147483648
Default Value - 64 bit: 9223372036854775807
Data Type: numeric
Default Value - 32 bit: 4294967295
Default Value - 64 bit: 18446744073709547520
Range - 32-bit: 7 to 4294967295
Range - 64-bit: 7 to 18446744073709547520
BACKUP_ALL: Same as BACKUP but also backs up the .MYI index file. t.MYI are saved as t.MYI-datetime.BAK.
DEFAULT: Recovers without backing up, forcing, or quick checking.
FORCE: Runs the recovery even if it determines that more than one row from the data file are lost.
QUICK: Does not check rows in the table if there are no delete blocks.
Command line: --myisam-recover-options[=name]
Scope: Global
Dynamic: No
Data Type: enumeration
Default Value:
BACKUP,QUICK (>= )
DEFAULT (<= )
OFF
Valid Values: OFF, DEFAULT, BACKUP, BACKUP_ALL, FORCE or QUICK
Data Type: numeric
Default Value: 1
Range - 32-bit: 1 to 4294967295
Range - 64-bit: 1 to 18446744073709547520
Data Type: BIGINT UNSIGNED
Default Value: 134216704
Range:
(>= MariaDB 10.11.5, MariaDB 10.6.15): 4096 to 1152921504606846975
(<= MariaDB 10.11.4, MariaDB 10.6.14): 4096 to 18446744073709551615
Scope: Global, Session
Dynamic: Yes
Data Type: enumeration
Default Value: NULLS_UNEQUAL
Valid Values: NULLS_UNEQUAL, NULLS_EQUAL, NULLS_IGNORED
Data Type: boolean
Default Value: OFF