A MySQL database gives you a choice of database engines and an easy way to switch them.
Default MySQL Engine would be enough for most of your application but in certain circumstances where the other available engines may be better suited to the task at hand.
Choose your engine
The number of database engines available to you depends on how your installation of MySQL was compiled. To add a new engine, MySQL must be recompiled. The concept of compiling an application just to add a feature may seem odd to Windows developers, but in the UNIX world, it’s the norm. By default, MySQL supports three database engines: ISAM, MyISAM, and HEAP. Two other types, InnoDB and Berkley (BDB), are often available as well.
ISAM
ISAM is a well-defined, time-tested method of managing data tables, designed with the idea that a database will be queried far more often than it will be updated. As a result, ISAM performs very fast read operations and is very easy on memory and storage resources. The two main downsides of ISAM are that it doesn’t support transactions and isn’t fault-tolerant: If your hard drive crashes, the data files will not be recoverable. If you’re using ISAM in a mission-critical application, you’ll want to have a provision for constantly backing up all your live data, something MySQL supports through its capable replication features.
MyISAM
MyISAM is MySQL’s extended ISAM format and default database engine. In addition to providing a number of indexing and field management functions not available in ISAM, MyISAM uses a table-locking mechanism to optimize multiple simultaneous reads and writes. The trade-off is that you need to run the OPTIMIZE TABLE command from time to time to recover space wasted by the update algorithms. MyISAM also has a few useful extensions such as the MyISAMChk utility to repair database files and the MyISAMPack utility for recovering wasted space.
MyISAM, with its emphasis on speedy read operations, is probably the major reason MySQL is so popular for Web development, where the vast majority of the data operations you’ll be carrying out are read operations. As a result, most hosting and Internet Presence Provider (IPP) companies will allow the use of only the MyISAM format.
HEAP
HEAP allows for temporary tables that reside only in memory. Residing in memory makes HEAP faster than ISAM or MyISAM, but the data it manages is volatile and will be lost if it’s not saved prior to shutdown. HEAP also doesn’t waste as much space when rows are deleted. HEAP tables are very useful in situations where you might use a nested SELECT statement to select and manipulate data. Just remember to destroy the table after you’re done with it. Let me repeat that: Don’t forget to destroy the table after you’re done with it.
InnoDB and Berkley DB
The InnoDB and Berkley DB (BDB) database engines are direct products of the technology that makes MySQL so flexible, the MySQL++ API. Almost every challenge you’re likely to face when using MySQL stems directly from the fact that the ISAM and MyISAM database engines aren’t transactional and lack foreign-key support. Although much slower than the ISAM and MyISAM engines, InnoDB and BDB include the transactional and foreign-key support missing from the former two choices. As such, if your design requires either or both of these features, you’re actually compelled to use one of these two choices.
If you’re feeling particularly capable, you can create your own database engine using MySQL++. The API provides all the functions you need for working with fields, records, tables, databases, connections, security accounts, and all of the other myriad functions that make up a DBMS such as MySQL. Going too heavily into the API is beyond the scope of this article, but it’s important to know that MySQL++ exists and that it’s the technology behind MySQL’s swappable engines. Presumably, this model of plug-in database engines could even be used to build a native XML provider for MySQL. (Any MySQL++ developers reading this may consider that a request.)