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

62 Comments / SQL / MySQL Tips and Tutorials / By /

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.

Related Posts

62 thoughts on “MySQL Engines: InnoDB vs. MyISAM – A Comparison of Pros and Cons”

  1. Pingback: MySQL: How to change or convert MyISAM to InnoDB or vice versa?

  2. Pingback: [SOLVED] Convert MySQL for Windows files to Linux? - Page 2

  3. Pingback: Mid-August Update

  4. Monika

    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

  6. Artur Ejsmont

    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 🙂

  8. Shane

    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.

  9. SoldCat

    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.

    1. Yang Yang

      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.

  11. Innvo

    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.

  12. Pingback: MySQL 存储引擎 « 游戏人生

  13. Pingback: MySQL Performance Tips

  14. Pingback: MySQL 엔진 비교: InnoDB vs MyISAM « turtle9

  15. Guspe

    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.

  16. Pingback: How do I use multiple joins? - PHP

  21. Pingback: DBaaS : Amazon RDS – MySQL Storage Engines & Best Practices | kuliza

  23. BUGHUNTER

    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!

    1. Ethan

      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.

  26. gabe

    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/

  27. Pingback: InnoDB vs MyISAM « Shivsingh's Weblog

  32. salman

    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 😛

  37. Pingback: mysql max_user_connections, and innodb vs myisam | PHP Developer Resource

  39. Pingback: Software @ UNH » Reports from OSCON 2012

  40. jpolito

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

  41. Pingback: Make phpMyAdmin Show Exact Number of Records for InnoDB Tables

  42. Pingback: RedBeanPHP — легкая библиотека ORM для PHP — Приступая к работе

  43. Anthony

    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!

  47. Pingback: MySQL 性能优化 | 悠悠鹿

  48. Pingback: Which MySQL storage engine will be better in my situation

  50. Pingback: MySQL 性能 | 谁主沉浮

Comments are closed.

Scroll to Top