MySQL Engines: InnoDB vs. MyISAM – A Comparison of Pros and Cons

by Yang Yang on September 2, 2009

The 2 major types of table storage engines for MySQL databases are InnoDB and MyISAM. To summarize the differences of features and performance,

  1. InnoDB is newer while MyISAM is older.
  2. InnoDB is more complex while MyISAM is simpler.
  3. InnoDB is more strict in data integrity while MyISAM is loose.
  4. InnoDB implements row-level lock for inserting and updating while MyISAM implements table-level lock.
  5. InnoDB has transactions while MyISAM does not.
  6. InnoDB has foreign keys and relationship contraints while MyISAM does not.
  7. InnoDB has better crash recovery while MyISAM is poor at recovering data integrity at system crashes.
  8. MyISAM has full-text search index while InnoDB has not.

In light of these differences, InnoDB and MyISAM have their unique advantages and disadvantages against each other. They each are more suitable in some scenarios than the other.

Advantages of InnoDB

  1. InnoDB should be used where data integrity comes a priority because it inherently takes care of them by the help of relationship constraints and transactions.
  2. Faster in write-intensive (inserts, updates) tables because it utilizes row-level locking and only hold up changes to the same row that’s being inserted or updated.

Disadvantages of InnoDB

  1. Because InnoDB has to take care of the different relationships between tables, database administrator and scheme creators have to take more time in designing the data models which are more complex than those of MyISAM.
  2. Consumes more system resources such as RAM. As a matter of fact, it is recommended by many that InnoDB engine be turned off if there’s no substantial need for it after installation of MySQL.
  3. No full-text indexing.

Advantages of MyISAM

  1. Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables.
  2. Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources.
  3. Full-text indexing.
  4. Especially good for read-intensive (select) tables.

Disadvantages of MyISAM

  1. No data integrity (e.g. relationship constraints) check, which then comes a responsibility and overhead of the database administrators and application developers.
  2. Doesn’t support transactions which is essential in critical data applications such as that of banking.
  3. Slower than InnoDB for tables that are frequently being inserted to or updated, because the entire table is locked for any insert or update.

The comparison is pretty straightforward. InnoDB is more suitable for data critical situations that require frequent inserts and updates. MyISAM, on the other hand, performs better with applications that don’t quite depend on the data integrity and mostly just select and display the data.

Subscribe to Kavoir: blog feed

You should also read:

Monika August 28, 2010 at 1:52 am

Hi thanks a lot for this article—
I’m not a mysql guru ;)
but I understand your comparison very well and know I now that InnoDB is false for my project.

kindly regards

Monika

Joomla Outsource India September 10, 2010 at 3:37 pm

hey, thanks for the perfect information, was searching over net since 15 mins.. keep up good posts :)

Artur Ejsmont November 18, 2010 at 1:21 am

well stating that innodb is slower is not really true it depends very much on the profile of queries and times of slow innodb are long gone.

Id suggest looking deeper into the matter or maybe using mixed engines approach and not dismissing myisma just yet :)

Yang Yang November 28, 2011 at 9:23 pm

That’s a good point, Artur! Thanks!

meghana December 1, 2010 at 7:27 pm

hey thank you so much the article was very useful and simple to understand.

Shane December 30, 2010 at 1:02 am

Thanks for this post. I got your link by searching in google for “InnoDB vs. MyISAM”.

This is helpful, I have a clear understanding about these two.

SoldCat January 4, 2011 at 6:05 am

I will agree with Artur. myisam is slower in real life. I have experienced it on my own software. Today, you cannot build a good application with a lot of writes. 10 years ago you could get off with 90% reads and 10% writes. Today it’s more like 70% – 30%. On Facebook for example, reads are a lot more intensive. myisam locks down the whole table, which is slower in the start. With rising concurency, InnoDB will win this fight hands down.

On the other hand, what if you get serious with your application and need of foreign keys, transactions? InnoDB is today’s choice.

Yang Yang November 28, 2011 at 9:26 pm

True. However, believe it or not, static (in the sense that the site is neither frequently updated by the administrator nor the visitor / user) content sites are still dominating. I think MyISAM can still be a viable choice for some time. It would be better for read-intensive sites with moderate traffic on limited server resources.

shahzad January 26, 2011 at 10:40 am

Good Information

Innvo February 2, 2011 at 2:39 pm

InnoDB will soon be the default engine for new MySQL releases.

One thing that’s not mentioend is the different way that InnoDB and MyISAM store their indexes, with InnoDB typically being faster although consuming more disk/memory space.

Yang Yang November 28, 2011 at 9:21 pm

Thanks for the tip!

Guspe March 14, 2011 at 5:26 am

I get an understanding now reading this article, and dare to decide stay with MyISAM or switch to InnoDB. Applications that don’t quite depend on the data integrity and mostly just select and display the data => this is mostly what my app looks like. Thank you.

Doro July 1, 2011 at 1:34 pm

Thank you :)

Glenn Mech July 17, 2011 at 5:09 am

Hi intelligent points.. now why didn’t I consider these?

Kyathi July 21, 2011 at 7:52 pm

Cool! Keep posting

Jhon October 24, 2011 at 12:48 am

This article is really help me to understanding InnoDB n MyISAM.
Thanks this is very helpful.

Sinan E. October 27, 2011 at 4:06 pm

Thanks for this useful explanation.

BUGHUNTER November 4, 2011 at 10:22 am

I do not understand why you are not taking a look at postgresql – it is NOT more complicated or whatever – you will see after little time, that it is actually much easier to handle. And you get a complete database with all features you will ever need, it does not leave you alone with this kind of comparisons and decisions. Try it and you will never look back, I promise!

Ethan January 17, 2012 at 12:45 am

That’s not true for all cases. I actually converted my project, which started with Postgresql, to Mysql because Postgres does not have enough compact data types. I have millions of rows with several small numbers (foreign keys) in the rows. The smallest value you can store in PGSQL is smallint (16bits), and there is no unsigned. Mysql has tinyint (8 bits) and unsigned for all integers. It also has meduimint (24 bits) which PG lacks. The smallest date format in PG is DATE (32 bits), mysql’s DATE uses only 3 bytes. So considering compact data storage, mysql has several advantages over PG.

sravan November 17, 2011 at 8:59 pm

Thanks for clear explaination….

בונה אתרים November 28, 2011 at 7:45 pm

If I have table with a lot of reads, but ones a month I write to this table I should use MyISAM.
Am I right?

Yang Yang November 28, 2011 at 9:18 pm

That’s correct.

gabe December 12, 2011 at 7:23 am

MyISAM is not slower for writes. It is slower for concurrent writes. For large writes it is generally faster to use MyISAM due to lack of foreign keys. You can disable indexes on large writes and batch index later, but that can kill your db if you have concurrent reads going on at the same time. Which brings us to the topic of copying and backing up. MyISAM is usually more stable to use on both. For large tables it’s usually not possible but for smaller tables it is useful.

InnoDB can outperform MyISAM on many types of concurrent reads. See: http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/

Casas January 9, 2012 at 9:31 pm

very good explanation, thanks

Daniel Watson January 17, 2012 at 12:01 am

I used Both MyISAM and InnoDB. But i think MyISAM is slightly better as it is simpler and older.
Thanks for sharing.

gurpreet February 7, 2012 at 6:31 pm

thx for giving such an nice differences.it save alot time

salman February 22, 2012 at 1:05 pm

perfect! u did a great job…

salman February 22, 2012 at 1:08 pm

though u did great but comparing all data types with examples an scenarios will be an ideal thing. Also discussing types used for transactional and decision support system will be great. You can contact me on my email :P

Mua sam vui February 26, 2012 at 1:21 pm

Thanks, i have changed to inodb

sathyamoorthi March 22, 2012 at 12:32 pm

within 5 minutes i got clear understanding. awesome simple post. thanks.

wong April 10, 2012 at 5:16 pm

awesome and simple post. Thanks for the info

ankur May 6, 2012 at 9:09 pm

Hey,This is really great help for me in my project.

Mvorisek July 5, 2012 at 6:53 pm

Great! M.

jpolito July 27, 2012 at 1:10 am

Just to update all of you guys, since MySQL 5.5 launched, and even before that, things were already changing. The guys at oracle are tunning innodb even more, and the results are documented in a whitepaper. You can get more info here https://blogs.oracle.com/MySQL/entry/comparing_innodb_to_myisam_performance.

I’ve been working with mysql for year, but only recently, as I was studying the differences and advantages of mysql 5.5 version, I knew about this change. In the past I would always go for MYISAM in most simple read intensive applications.

The article in the link above, states this, right in the second paragraph:

“Compared to MyISAM, InnoDB delivered 35x higher throughput on the Read / Write test and 5x higher throughput on the Read-Only test, with 90% scalability across 36 CPU cores.”

Anthony September 11, 2012 at 8:43 pm

Great Article!!!

Well done!

I have worked with database engines since 1986 (developed a custom one myself in 1989).

Seems like you have a thorough understanding of MySQL and I like MySQL because it’s simplicity and OPEN-SOURCE (did I say OPEN SOURCE?) nature.

Well done!

saradindu September 25, 2012 at 1:47 pm

Good article !!

Jakeroid October 4, 2012 at 3:29 pm

Thx for article. Now I can chose that db engine I need.

pankaj October 7, 2012 at 7:25 pm

That’s an excellent explanation please keep doing such kind of work

thanks

Moemen November 10, 2012 at 7:11 am

Very good article, thanks.

Vivek Shrivastava November 28, 2012 at 5:36 pm

Nice Article thanks.

Islam December 3, 2012 at 10:41 pm

one of the best articles i ever read a bout the different between InnoDB and MyISAM

Good job man

Vali February 8, 2013 at 5:36 pm
Hansolefsen February 12, 2013 at 6:12 pm

Hi, gurus of Mysql tweaking, is there a way to check for integrity in InnoBD, because sometimes when I’m working with Symfony2.0 it shows me incorrect exceptions that the Mysql validation is not completed
A Database Error Occurred

Error Number: 1452

Cannot add or update a child row: a foreign key constraint fails (midas1.procedure, CONSTRAINT belongs to a FOREIGN KEY (department_id) REFERENCES department (department_id))

INSERT INTO procedure (patient_id, name_id, department_id, dosage_id, edocument, user_id, duration, submitted, comment) VALUES (17, 7, 0, NULL, NULL, ’2′, NULL, ’06-07-2011 13:41:01′, NULL)

Comments on this entry are closed.

{ 19 trackbacks }

Previous post:

Next post: