MyISAM

Last updated
MyISAM
Developer(s) Oracle Corporation
Written in C
Operating system Cross-platform
Type Database engine
License GNU General Public License
Website dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html

MyISAM was the default storage engine for the MySQL relational database management system versions prior to 5.5 released in December 2009. [1] It is based on the older ISAM code, but it has many useful extensions.

Contents

Filesystem

Each MyISAM table is stored on disk in three files (if it is not partitioned). The files have names that begin with the table name and have an extension to indicate the file type. MySQL uses a .frm file to store the definition of the table, but this file is not a part of the MyISAM engine; instead it is a part of the server. The data file has a .MYD (MYData) extension. The index file has a .MYI (MYIndex) extension. The index file, if lost, can always be recreated by recreating indexes.

Files format depends on the ROW_FORMAT table option. The following formats are available:

MyISAM files do not depend on the system and, since MyISAM is not transactional, their content does not depend on current server workload. Therefore it is possible to copy them between different servers.

Features

MyISAM is optimized for environments with heavy read operations, and few writes, or none at all. A typical area in which one could prefer MyISAM is data warehouse because it involves queries on very big tables, and the update of such tables is done when the database is not in use (usually at night).

The reason MyISAM allows for fast reads is the structure of its indexes: each entry points to a record in the data file, and the pointer is offset from the beginning of the file. This way records can be quickly read, especially when the format is FIXED. Thus, the rows are of constant length. Inserts are easy too because new rows are appended to the end of the data file. However, delete and update operations are more problematic: deletes must leave an empty space, or the rows' offsets would change; the same goes for updates, as the length of the rows becomes shorter; if the update makes the row longer, the row is fragmented. To defragment rows and claim empty space, the OPTIMIZE TABLE command must be executed. Because of this simple mechanism, MyISAM index statistics are usually quite accurate.

However, the simplicity of MyISAM has several drawbacks. The major deficiency of MyISAM is the absence of transactions support. Also, foreign keys are not supported. In normal use cases, InnoDB seems to be faster than MyISAM. [2]

Versions of MySQL 5.5 and greater have switched to the InnoDB engine to ensure referential integrity constraints, and higher concurrency.

MyISAM supports FULLTEXT indexing and OpenGIS data types.

Forks

MariaDB has a storage engine called Aria, which is described as a "crash-safe alternative to MyISAM". [3] However, the MariaDB developers still work on MyISAM code. The major improvement is the "Segmented Key Cache". [4] If it is enabled, MyISAM indices' cache is divided into segments. This improves the concurrency because threads rarely need to lock the entire cache.

In MariaDB, MyISAM also supports virtual columns.

Drizzle does not include MyISAM.

See also

Notes

  1. ↑ "MySQL 5.5 Reference Manual :: 13 Storage Engines :: 13.6 The InnoDB Storage Engine". 2009-05-10. Archived from the original on 2010-11-20. Retrieved 2021-03-16.
  2. ↑ "MySQL Performance: InnoDB vs MyISAM in 5.6". 2012-11-16. Retrieved 2021-03-16.
  3. ↑ "Aria FAQ". MariaDB. 2010-08-15. Retrieved 2021-03-16.
  4. ↑ "Segmented Key Cache". MariaDB. 2010-08-17. Retrieved 2021-03-16.

Related Research Articles

<span class="mw-page-title-main">MySQL</span> SQL database engine software

MySQL is an open-source relational database management system (RDBMS). Its name is a combination of "My", the name of co-founder Michael Widenius's daughter My, and "SQL", the acronym for Structured Query Language. A relational database organizes data into one or more data tables in which data may be related to each other; these relations help structure the data. SQL is a language that programmers use to create, modify and extract data from the relational database, as well as control user access to the database. In addition to relational databases and SQL, an RDBMS like MySQL works with an operating system to implement a relational database in a computer's storage system, manages users, allows for network access and facilitates testing database integrity and creation of backups.

A database engine is the underlying software component that a database management system (DBMS) uses to create, read, update and delete (CRUD) data from a database. Most database management systems include their own application programming interface (API) that allows the user to interact with their underlying engine without going through the user interface of the DBMS.

ISAM, an acronym for Indexed Sequential Access Method, is a method for creating, maintaining, and manipulating computer files of data so that records can be retrieved sequentially or randomly by one or more keys. Indexes of key fields are maintained to achieve fast retrieval of required file records in indexed files. IBM originally developed ISAM for mainframe computers, but implementations are available for most computer systems.

The following tables compare general and technical information for a number of relational database management systems. Please see the individual products' articles for further information. Unless otherwise specified in footnotes, comparisons are based on the stable versions without any add-ons, extensions or external programs.

InnoDB is a storage engine for the database management system MySQL and MariaDB. Since the release of MySQL 5.5.5 in 2010, it replaced MyISAM as MySQL's default table type. It provides the standard ACID-compliant transaction features, along with foreign key support. It is included as standard in most binaries distributed by MySQL AB, the exception being some OEM versions.

Extensible Storage Engine (ESE), also known as JET Blue, is an ISAM data storage technology from Microsoft. ESE is the core of Microsoft Exchange Server, Active Directory, and Windows Search. It's also used by a number of Windows components including Windows Update client and Help and Support Center. Its purpose is to allow applications to store and retrieve data via indexed and sequential access.

The Access Database Engine is a database engine on which several Microsoft products have been built. The first version of Jet was developed in 1992, consisting of three modules which could be used to manipulate a database.

<span class="mw-page-title-main">Virtuoso Universal Server</span> Computer software

Virtuoso Universal Server is a middleware and database engine hybrid that combines the functionality of a traditional relational database management system (RDBMS), object–relational database (ORDBMS), virtual database, RDF, XML, free-text, web application server and file server functionality in a single system. Rather than have dedicated servers for each of the aforementioned functionality realms, Virtuoso is a "universal server"; it enables a single multithreaded server process that implements multiple protocols. The free and open source edition of Virtuoso Universal Server is also known as OpenLink Virtuoso. The software has been developed by OpenLink Software with Kingsley Uyi Idehen and Orri Erling as the chief software architects.

Falcon is a discontinued transactional storage engine being developed for the MySQL relational database management system. Development was stopped after Oracle purchased MySQL. It was based on the Netfrastructure database engine. Falcon was designed to take advantage of Sun's ZFS file system.

Archive is a storage engine for the MySQL relational database management system. Users can use this analytic storage engine to create a table that is “archive” only. Data cannot be deleted from this table, only added. The Archive engine uses a compression strategy based on the zlib library and it packs the rows using a bit header to represent nulls and removes all whitespace for character type fields. When completed, the row is inserted into the compression buffer and flushed to disk by an explicit flush table, a read, or the closing of the table.

Microsoft SQL Server is a proprietary relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network. Microsoft markets at least a dozen different editions of Microsoft SQL Server, aimed at different audiences and for workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users.

Aria is a storage engine for the MariaDB and MySQL relational database management systems. Its goal is to make a crash-safe alternative to MyISAM. It is not transactional.

The MySQLi Extension is a relational database driver used in the PHP scripting language to provide an interface with MySQL databases.

InfinityDB is an all-Java embedded database engine and client/server DBMS with an extended java.util.concurrent.ConcurrentNavigableMap interface that is deployed in handheld devices, on servers, on workstations, and in distributed settings. The design is based on a proprietary lockless, concurrent, B-tree architecture that enables client programmers to reach high levels of performance without risk of failures.

TokuDB is an open-source, high-performance storage engine for MySQL and MariaDB. It achieves this by using a fractal tree index. It is scalable, ACID and MVCC compliant, provides indexing-based query improvements, offers online schema modifications, and reduces replication lag for both hard disk drives and flash memory.

This is a comparison between notable database engines for the MySQL database management system (DBMS). A database engine is the underlying software component that a DBMS uses to create, read, update and delete (CRUD) data from a database.

In relational databases a virtual column is a table column whose value(s) is automatically computed using other columns values, or another deterministic expression. Virtual columns are defined of SQL:2003 as Generated Column, and are only implemented by some DBMSs, like MariaDB, SQL Server, Oracle, PostgreSQL, SQLite and Firebird.

The following outline is provided as an overview of and topical guide to MySQL:

MEMORY is a storage engine for MySQL and MariaDB relational database management systems, developed by Oracle and MariaDB. Before the version 4.1 of MySQL it was called Heap.

This page is based on this Wikipedia article
Text is available under the CC BY-SA 4.0 license; additional terms may apply.
Images, videos and audio are available under their respective licenses.