What is the difference between InnoDB and MyISAM?

Question

I am using MySQL as my database for my current web project. I am new to MySQL. Please explain to me the difference between InnoDB and MyISAM.

Solution 1

ISAM = Indexed Sequential Access Method and is essentially a flat file (for those DBAs who can remember, think Btrieve, or B-Tree). It's a very old technology - but don't let that put you off using it. Because it's a flat file (more on that later), it is not relational, and thus is not an RDBMS, and thus is more appropriate in some situations.

InnoDB is the full RDBMS like you are most likely familiar with. MyISAM can appear to be relational through another layer added on top that maintains your links, logic and referential integrity.

ISAM is brilliant if you have a LOT of records (like, 20 million), and the records are mostly stand-alone (i.e. you don't need to do lots of links to retrieve associated data). It relies VERY heavilly on indexes and if you don't have the right index, be prepared for very very long query times. Case in point: We had a Btrieve ISAM table with 20M+ records and to do a retrieve and filter data based on an accurate index was almost instant. Using the wrong index was literally 15 minutes.

InnoDB is great for if you have a lot of relational links. Table A references a field in Table B, which references Table C and D. InnoDB can fetch these records using all sorts of nice joining methods (hash joins, etc), whereas an ISAM database would have to run multiple sub-queries for every single row and match the records manually.

You will really have to do a course in databases if you want much more detail than that!

Solution 2

The most fundamental is that InnoDB is transactional. MyIsam isn't. MyIsam is generally a bit faster, so if you don't need transactions that is generally your best bet. For detailed descriptions you should read the MySQL documentation.

Solution 3

These days unless you're using a table for log style data (many more INSERT's then SELECT's, no transactions) InnoDB is usually faster, more reliable, has more features, etc.

The only other feature MyISAM has is the fulltext search which is fine for basic use, but most people use some from of Lucene for anything serious.

Either way you must tune MySQL as the defaults are essentially set for a 32MB pentium that's shared with other services.

Solution 4

A bit late to the game...but here's a quite comprehensive post I wrote a few months back, detailing the major differences between MYISAM and InnoDB. Grab a cuppa (and maybe a biscuit), and enjoy.

---

The major difference between MyISAM and InnoDB is in referential integrity and transactions. There are also other difference such as locking, rollbacks, and full-text searches.

Referential Integrity

Referential integrity ensures that relationships between tables remains consistent. More specifically, this means when a table (e.g. Listings) has a foreign key (e.g. Product ID) pointing to a different table (e.g. Products), when updates or deletes occur to the pointed-to table, these changes are cascaded to the linking table. In our example, if a product is renamed, the linking table’s foreign keys will also update; if a product is deleted from the ‘Products’ table, any listings which point to the deleted entry will also be deleted. Furthermore, any new listing must have that foreign key pointing to a valid, existing entry.

InnoDB is a relational DBMS (RDBMS) and thus has referential integrity, while MyISAM does not.

Transactions & Atomicity

Data in a table is managed using Data Manipulation Language (DML) statements, such as SELECT, INSERT, UPDATE and DELETE. A transaction group two or more DML statements together into a single unit of work, so either the entire unit is applied, or none of it is.

MyISAM do not support transactions whereas InnoDB does.

If an operation is interrupted while using a MyISAM table, the operation is aborted immediately, and the rows (or even data within each row) that are affected remains affected, even if the operation did not go to completion.

If an operation is interrupted while using an InnoDB table, because it using transactions, which has atomicity, any transaction which did not go to completion will not take effect, since no commit is made.

Table-locking vs Row-locking

When a query runs against a MyISAM table, the entire table in which it is querying will be locked. This means subsequent queries will only be executed after the current one is finished. If you are reading a large table, and/or there are frequent read and write operations, this can mean a huge backlog of queries.

When a query runs against an InnoDB table, only the row(s) which are involved are locked, the rest of the table remains available for CRUD operations. This means queries can run simultaneously on the same table, provided they do not use the same row.

This feature in InnoDB is known as concurrency. As great as concurrency is, there is a major drawback that applies to a select range of tables, in that there is an overhead in switching between kernel threads, and you should set a limit on the kernel threads to prevent the server coming to a halt.

Transactions & Rollbacks

When you run an operation in MyISAM, the changes are set; in InnoDB, those changes can be rolled back. The most common commands used to control transactions are COMMIT, ROLLBACK and SAVEPOINT. 1. COMMIT - you can write multiple DML operations, but the changes will only be saved when a COMMIT is made 2. ROLLBACK - you can discard any operations that have not yet been committed yet 3. SAVEPOINT - sets a point in the list of operations to which a ROLLBACK operation can rollback to

Reliability

MyISAM offers no data integrity - Hardware failures, unclean shutdowns and canceled operations can cause the data to become corrupt. This would require full repair or rebuilds of the indexes and tables.

InnoDB, on the other hand, uses a transactional log, a double-write buffer and automatic checksumming and validation to prevent corruption. Before InnoDB makes any changes, it records the data before the transactions into a system tablespace file called ibdata1. If there is a crash, InnoDB would autorecover through the replay of those logs.

FULLTEXT Indexing

InnoDB does not support FULLTEXT indexing until MySQL version 5.6.4. As of the writing of this post, many shared hosting providers’ MySQL version is still below 5.6.4, which means FULLTEXT indexing is not supported for InnoDB tables.

However, this is not a valid reason to use MyISAM. It’s best to change to a hosting provider that supports up-to-date versions of MySQL. Not that a MyISAM table that uses FULLTEXT indexing cannot be converted to an InnoDB table.

Conclusion

In conclusion, InnoDB should be your default storage engine of choice. Choose MyISAM or other data types when they serve a specific need.

Solution 5

Generally, the rule of thumb is, if you want speed, use MyISAM and if you want stability, use InnoDB. Something to do with atomicity if I remember correctly.

View more solutions

Comments

[Johan]

feel free to add a MySQL tagg as well :)

[Craig Vermeer]

Doesn't this belong on stackoverflow?

[TRiG]

@ripper234. Two better versions of this post (far more background; more demonstration of prior research) exist on SO: stackoverflow.com/q/12614541/209139 and stackoverflow.com/q/20148/209139.

[LapTop006]

Excecpt that's no longer true, InnoDB is actually faster in most cases.

[LapTop006]

ISAM is just as relational as anything else, it's just not optimised for it.

[Johan]

can someone add a link some benchmarks or something...?

[Admin]

thanks 4 your brief description. Now basically i am cleared