April 20

MyISAM or InnoDB MySQL engine?

If you’re curious about the difference of MySQL engines, here they are.

Comparing and contrasting what MyISAM is and InnoDB. First ISAM is the acronym for Indexed Sequential Access Method, generally it is fast, compressable, and allows full text searching.  Second InnoDB is transaction safe and includes automatic foreign key checking so it’s a bit slower.

The important part is that don’t spend too much time deciding which one to choose, because you can change table type later with the ALTER TABLE statement.  Instead you should focus on optimizing tables for performance such as using fixed with fields versus variable width (varchar).  Now for the technical details.

MyISAM

MyISAM tables are optimized for flexibility and compressionThey can hold upto 256TB of data but are not transactional-safe, meaning if the power goes out in a middle of a write, the data is gone.  This is especially important for financial data.  The schema is flexible so it is good for beginners learning about databases.  Foreign key relationships do not have to be setup for the database to function, however this can cause poor performance and unreliable data.  The simplicity allows lower use of hardware resources and another benefit is the table search ability for full-text.
Continue reading

Category: Code | LEAVE A COMMENT