Difference between revisions of "MySQL Engines MyISAM and Innodb"
(Created page with "First off, it should be noted that MyISAM and Innodb are not the only two engines that MySQL supports. These are the two most common engines. A MySQL database can have its tabl...") |
|||
| Line 3: | Line 3: | ||
spencer7593 of Stack Overflow, [http://stackoverflow.com/questions/12614541/whats-the-difference-between-myisam-and-innodb The Difference Between MyISAM and Innodb] wrote, | spencer7593 of Stack Overflow, [http://stackoverflow.com/questions/12614541/whats-the-difference-between-myisam-and-innodb The Difference Between MyISAM and Innodb] wrote, | ||
| − | ''The main differences between InnoDB and MyISAM ("with respect to designing a table or database" you asked about) are support for "referential integrity" and "transactions". | + | ''The main differences between InnoDB and MyISAM ("with respect to designing a table or database" you asked about) are support for "referential integrity" and "transactions".'' |
| − | If you need the database to enforce foreign key constraints, or you need the database to support transactions (i.e. changes made by two or more DML operations handled as single unit of work, with all of the changes either applied, or all the changes reverted) then you would choose the InnoDB engine, since these features are absent from the MyISAM engine. | + | ''If you need the database to enforce foreign key constraints, or you need the database to support transactions (i.e. changes made by two or more DML operations handled as single unit of work, with all of the changes either applied, or all the changes reverted) then you would choose the InnoDB engine, since these features are absent from the MyISAM engine.'' |
| − | Those are the two biggest differences. Another big difference is concurrency. With MyISAM, a DML statement will obtain an exclusive lock on the table, and while that lock is held, no other session can perform a SELECT or a DML operation on the table. | + | ''Those are the two biggest differences. Another big difference is concurrency. With MyISAM, a DML statement will obtain an exclusive lock on the table, and while that lock is held, no other session can perform a SELECT or a DML operation on the table. |
| + | '' | ||
| + | ''Those two specific engines you asked about (InnoDB and MyISAM) have different design goals. MySQL also has other storage engines, with their own design goals.'' | ||
| − | + | ''So, in choosing between InnoDB and MyISAM, the first step is in determining if you need the features provided by InnoDB. If not, then MyISAM is up for consideration.'' | |
| − | + | ||
| − | So, in choosing between InnoDB and MyISAM, the first step is in determining if you need the features provided by InnoDB. If not, then MyISAM is up for consideration.'' | + | |
| | ||
Revision as of 21:31, 21 January 2014
First off, it should be noted that MyISAM and Innodb are not the only two engines that MySQL supports. These are the two most common engines. A MySQL database can have its tables utilize a mix of different table engine types or all of the same type. MyISAM is the default table engine type for MySQL 5.0. Distributions such as Redhat, Fedora, and CentOS tend to default to Innodb.
spencer7593 of Stack Overflow, The Difference Between MyISAM and Innodb wrote,
The main differences between InnoDB and MyISAM ("with respect to designing a table or database" you asked about) are support for "referential integrity" and "transactions".
If you need the database to enforce foreign key constraints, or you need the database to support transactions (i.e. changes made by two or more DML operations handled as single unit of work, with all of the changes either applied, or all the changes reverted) then you would choose the InnoDB engine, since these features are absent from the MyISAM engine.
Those are the two biggest differences. Another big difference is concurrency. With MyISAM, a DML statement will obtain an exclusive lock on the table, and while that lock is held, no other session can perform a SELECT or a DML operation on the table. Those two specific engines you asked about (InnoDB and MyISAM) have different design goals. MySQL also has other storage engines, with their own design goals.
So, in choosing between InnoDB and MyISAM, the first step is in determining if you need the features provided by InnoDB. If not, then MyISAM is up for consideration.
