While definitely not a new topic, this blog post will go over the two most common database engines in MySQL: MyISAM and InnoDB.
MyISAM is typically the default database engine in MySQL. MyISAM supports full-text indexes and is friendly to heavy reads. A potential downside to using MyISAM is that write (INSERT/UPDATE) operations lock the entire database table, which could cause issues when many users are trying to write to said table. Problems resulting from locking the entire table can include a slowdown in website responsiveness, 500 errors, and increased server resource utilization as more write requests are forced to be queued for processing… and sometimes such a virtual traffic jam can even crash the database server, causing the potential for data loss.
InnoDB is another common and well established database engine in MySQL. InnoDB supports transactions, foreign keys, and is friendly to heavy writes. Unlike MyISAM, InnoDB write operations lock only the database row in question instead of the entire database. This design allows multiple simultaneous writes without much resource contention, increasing responsiveness in multiuser CRUD-type web applications. InnoDB has the added bonus of being ACID compliant, and has a greater chance to successfully recover from database crashes.
In summary, if you have a database table that is read from more than it is written to, use MyISAM; if you have a database table that is written to more than it is read from, or is potentially written to by many concurrent users, use InnoDB.
