≡ Menu

MyIsam Vs Innodb Vs Memory – Mysql Storage Engine Comparison

If you’ve decided to use MySQL database for your enterprise application, you should wisely choose the MySQL storage engine type that will be appropriate for your situation.

Depending on the situation, a particular storage engine can be a best fit or worst fit.

Once you understand the differences between all MySQL storage engines, it will help you to take advantage of the flexibility of using different storage engines for different tables.

With the right selection of the MySQL engine that suites your requirements, you can get excellent performance, and can optimize your MySQL database effectively.

Pros

  • MyISAM is best suited for high “select” query rate, and non transactional operations.
  • InnoDB is best suited for parallel insert/update/delete operations (because of row level locking), and transactional operations (because of roll back feature).
  • Memory engine (HEAP) is best suited for on the fly fast data access, as everything is stored in the RAM.

Cons

  • MyISAM is worst for high “insert/update” query rate. (because of table level locking).
  • InnoDB is worst when there is combination of non-transactional and read only operations.
  • Memory engine is worst for long term usage (because Of data integrity issues) and transactional operations.

Differences

The following table summarizes the key differences between these three types of engines. Only those features that differs between these engines are listed, excluding those features that are present in all three engines.

Feature MyISAM InnoDB Memory
ACID Transaction No Yes No
Configurable ACID Properties No Yes No
Crash Safe No Yes No (RAM)
Foreign Key Support No Yes No
Multi Version Concurrency Control (MVCC) No Yes No
Geospatial datatype Yes Yes No
Geospatial indexing Yes No No
Full-text Search Index Yes No No
Data Cache No Yes N/A
Compressed Data Yes Yes No
Storage Limits 256TB 64TB RAM
Storage Cost Low High N/A
Memory Cost Low High Medium
Locking Granularity Table Row Table

.

If you enjoyed this article, you might also like..

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book

{ 10 comments… add one }

  • Frank February 25, 2014, 4:43 am

    Thank you so much.
    Good information.

  • Ivan February 25, 2014, 6:06 am

    Thanks, very good article. How can I know what am I using ?

  • Arash February 25, 2014, 6:55 am

    Hi,Thanks for article
    but there something different about full text index support in innodb storage engine and that is introduction of this feature in MySQL 5.6.

  • Devang Swami February 25, 2014, 7:51 am

    Very good article

  • duskoKoscica February 26, 2014, 5:54 am

    No foreign key, means no thank you! Nice article.

  • Marcus February 28, 2014, 12:36 am

    IMHO ..

    if you write about “enterprise” applications, you must write about backup and restore, too. And from my point of view this is the worst feature of the myisam engine, as it is incapable to do online backups.

    Another “nice” feature of myisam tables and mysql in general are transactions, as you correctly write, myisam tables do not support transactions, MySQL will let you do something like:
    start transaction; truncate my_mysam_table; rollback transaction

    Every single command in this row will be acknowledged by the mysql server with an “OK”, but it won’t do what you expect ..

    And one last point in the feature comparison table, the innodb storage engine does support fulltext indexing these days. It did not in older versions, but since the release of 5.6 it does.

  • duskoKoscica February 28, 2014, 8:26 am

    No transaction support. Do I pray to God or … there is a way to go around, like you use like tar and other things, but there are some alternative programs that could help you with it.

    So how do we go around with those situations. God thing 256 TB.

  • Pratik March 11, 2014, 11:54 pm

    Thanks for this valuable information

  • Jouni "rautamiekka" Järvinen July 25, 2014, 5:11 pm

    Ever since 5.6, and before that, MyISAM is made jokes out of by InnoDB, especially if running optimized Percona Server: everything MyISAM could do, InnoDB does better, and more. Thankful for release of 5.6, InnoDB supports FULLTEXT INDEX which was plankiller before its arrival.

    We’re not trusting MyISAM for anything but testing shit out for minimal space usage that won’t be seeing usage anytime soon, if ever; if those are lost, little bother. Even then, I’m more likely to make InnoDB tables than MyISAM.

    The only thing I miss is support for very highly large tables; MI 256TB, ID 64TB. Although reaching that can’t happen for someone not as big as Google, someone like Google would appreciate something bigger, but I’m sure they have the means to make a table larger than that.

  • Mahdi September 23, 2014, 1:51 am

    I think just on one condition you should consider using MYISAM over INNODB, and that’s when you want to use the table as a log table. that’s the power MYISAM!

Leave a Comment