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 |
.
My name is Ramesh Natarajan. I will be posting instruction guides, how-to, troubleshooting tips and tricks on Linux, database, hardware, security and web. My focus is to write articles that will either teach you or help you resolve a problem. Read more about
Comments on this entry are closed.
Thank you so much.
Good information.
Thanks, very good article. How can I know what am I using ?
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.
Very good article
No foreign key, means no thank you! Nice article.
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.
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.
Thanks for this valuable information
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.
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!