What's the difference between MyISAM and InnoDB?

Question

I understand that this question has been asked before, but most of the time it is asked in relation to a specific database or table. I cannot find an answer on this site that describes the two engines and their differences without respect to someones specific database.

I want to be able to make more informed decisions in the future with respect to designing a table or database, so am looking for a comprehensive answer on the differences between the two storage engines.

What's the difference between MyISAM and InnoDB, and what should I be looking for when trying to decide between one or the other?

Solution 1

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".

We choose InnoDB if we need the database to enforce foreign key constraints or 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). These features are not supported by 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.

In choosing between InnoDB and MyISAM, the first step is to determine if we need the features provided by InnoDB. If not, then MyISAM is up for consideration.

A more detailed discussion of differences is rather impractical (in this forum) absent a more detailed discussion of the problem space... how the application will use the database, how many tables, size of the tables, the transaction load, volumes of select, insert, updates, concurrency requirements, replication features, etc.

---

The logical design of the database should be centered around data analysis and user requirements; the choice to use a relational database would come later, and even later would the choice of MySQL as a relational database management system, and then the selection of a storage engine for each table.

Solution 2

MYISAM:

1. MYISAM supports Table-level Locking
2. MyISAM designed for need of speed
3. MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS
4. MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
5. MYISAM not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command it’s done.
6. MYISAM supports fulltext search
7. You can use MyISAM, if the table is more static with lots of select and less update and delete.

INNODB:

1. InnoDB supports Row-level Locking
2. InnoDB designed for maximum performance when processing high volume of data
3. InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS
4. InnoDB stores its tables and indexes in a tablespace
5. InnoDB supports transaction. You can commit and rollback with InnoDB

Comments

[LittleBobbyTables - Au Revoir]

This question is currently being discussed on Meta

[Rachel]

Hi Scott, I made a slight edit to the wording of your question to try and get it reopened. If I've changed your question too much, feel free to edit it further or roll back the changes. I think the question is a good one to ask, and the answer given below is great. I've voted to reopen it, but it still needs 4 other votes to be reopened.

[Scott]

I appreciate the edit and any help to reopen. I think this is an important question with a good answer.

[Bart]

@Rachel I'm sure we'll disagree here, but didn't you just turn this question into a not constructive one? i.e. list of differences, pros and cons, opinions...

[Rachel]

@Bart I don't think so, because the two technologies are very similar and used by the same process for the same thing (see wikipedia links in question), and there are only a handful of differences between them. The end result should not be a long list of answers like most not-constructive questions, but rather just one or possibly two that points out what those few differences are and how that affects when you should use one over the other.

[Jocelyn]

Other similar questions asked earlier: MyISAM versus InnoDB, Difference between InnoDB and MyISAM?, What are the main differences between InnoDB and MyISAM?.

[nickhar]

I think this is a great question, but one that will always be best answered with 'It depends!'. It's too subjective and as the first answer has alluded to, requires an understanding of what you're trying to achieve - something that you've not covered in your question.

[Tushar Trivedi]

rorguide.blogspot.in/2011/06/…

[aneroid]

+1 "referential integrity" and "transactions" but I expect this question will be closed soon.

[spencer7593]

NOTE: InnoDB can store individual tables in separate files, similar to MyISAM, if the innodb_file_per_table option is set when the table is created.

[RandomSeed]

InnoDB support of Fulltext indexes was introduced in v5.6.

[transilvlad]

Fulltext indexes are not so handy in InnoDB if you have few inserts/updates/deletes but loads of selects.

[Domi]

What do you mean by MyISAM designed for need of speed vs. InnoDB designed for maximum performance when processing high volume of data? What is diskspace vs table space? Why is it that You can use MyISAM, if the table is more static with lots of select and less update and delete? You can use InnoDB for that as well. Could you please provide arguments for your statements?

[medina]

Domi, your questions are out of scope. Just read the documentation, mate.

[Elangovan]

@innoDB after version 5.5 it will support full text search

[Finesse]

InnoDB has a big disadvantage: it doesn't remember auto increment values (it resets them to the highest ids in the tables when MySQL restarts). So if you use InnoDB and you need unique ids, you need to implement a workaround (e.g. keep the latest ids in a separate table).

[spencer7593]

@Finesse: My answer failed to enumerate all of the differences between InnoDB and MyISAM. But yes, InnoDB behavior with AUTO_INCREMENT is a difference, and one that we have to account for if we are going to use AUTO_INCREMENT, and if we are dependent on some behavior that isn't supported. This difference could be considered a disadvantage; perhaps some would consider it a "big disadvantage". Note that the initialization of AUTO_INCREMENT values has been modified in MySQL 8.0 dev.mysql.com/doc/refman/8.0/en/…

[oldboy]

for the sake of clarity, would SELECT * FROM table_a; INSERT INTO table_a (column_1) VALUES ('whatever') be considered a "transaction"?

[spencer7593]

@Anthony: In RDBMS, a "transaction" is a unit of work. Basically, a set of DML statements (one ore more) that is initiated with "START TRANSACTION" statement, and is either completed with a "COMMIT" statement, or undone with a "ROLLBACK" statement. As far as a consistency of resultsets within a transaction, that depends on the "isolation level". For sake of clarity, the MyISAM engine ignores ROLLBACK,COMMIT and START TRANSACTION statements. With MyISAM, each DML statement is treated as a separate transaction, operates as if AUTO_COMMIT is enabled.