MyIsam Vs Innodb Vs Memory – Mysql Storage Engine Comparison

by Luke P. Issac on February 24, 2014

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

.


Linux Sysadmin Course Linux provides several powerful administrative tools and utilities which will help you to manage your systems effectively. If you don’t know what these tools are and how to use them, you could be spending lot of time trying to perform even the basic administrative tasks. The focus of this course is to help you understand system administration tools, which will help you to become an effective Linux system administrator.
Get the Linux Sysadmin Course Now!

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

{ 9 comments… read them below or add one }

1 Frank February 25, 2014 at 4:43 am

Thank you so much.
Good information.

2 Ivan February 25, 2014 at 6:06 am

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

3 Arash February 25, 2014 at 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.

4 Devang Swami February 25, 2014 at 7:51 am

Very good article

5 duskoKoscica February 26, 2014 at 5:54 am

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

6 Marcus February 28, 2014 at 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.

7 duskoKoscica February 28, 2014 at 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.

8 Pratik March 11, 2014 at 11:54 pm

Thanks for this valuable information

9 Jouni "rautamiekka" Järvinen July 25, 2014 at 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.

Leave a Comment

Previous post:

Next post: