MySQL saves information by default with the MyISAM storage engine, but there are others including InnoDB (the most famous), ISAM, Heap, NBD, Berkeley DB or Merge. The choice of a storage engine is important and the different ones sometimes go unrecognized. This article presents the main differences between MyISAM and InnoDB to understand how to make this choice.
Here is a non-exhaustive list of arguments in favor of MyISAM:
- MySQL default storage system. This type of storage is the oldest available with MySQL
- Very fast for performing SELECT or INSERT queries
- Supports full text indexing (cf. full text index). Offers much better performance when searching on text fields
- Easier administration
- Possibility of locking at the level of a table (cf. table lock)
- More flexible in terms of data integrity (but that can have its flip side …)
The flexibility of MyISAM leads to some negative points when it comes to maintaining good data integrity, as the points below show:
- No transactions
- No foreign keys
- More difficult to recover after a crash. Data integrity can easily be compromised
Switch to MyISAM
If you want to pass a table to MyISAM all you have to do is run this SQL query:
ALTER TABLE `table` ENGINE=MYISAM;
The main advantages of using InnoDB are the ability to manage data integrity well, even during a server crash.
- Manages transactions (set of grouped requests) such as BEGIN, COMMIT, ROLLBACK …
- Manages foreign keys and integrity constraints
- ACID support to ensure all registrations are successful or unsuccessful. No risk of error, even in the event of a fault
- Crash recovery system thanks to a log replay system
However, data integrity makes the administration of a database difficult.
- Stricter regarding data integrity
- Does not support full text indexing (cf. full text index)
- Slightly more complex administration
- Bigger storage engine. It requires more resources and is slower
- Possibility of locking at row level (cf. row lock) to insert or update a record
- Interesting to note that this is newer, although this is not necessarily a disadvantage
Switch to InnoDB
To pass a table to InnoDB, just run the following query:
ALTER TABLE `table` ENGINE=InnoDB;
It is important to know that it is possible in the same database to have tables in MyISAM and others in InnoDB. It even improves performance in some cases.
To summarize on the choice between InnoDB and MyISAM:
- MyISAM: to be preferred when it is especially necessary to carry out requests to read or insert data. For websites, this is often the recommended solution.
- InnoDB: preferred for systems which must not contain errors and which require foreign keys. For an application handling important data, such as a banking application, this may be the recommended storage engine.
If you want to know more precisely the differences, you must of course go to the official documentation. Also, feel free to share your knowledge and experiences in the comments of this article.