I recently got a mail asking about MYSql database Engine. So i planned to put an article on it.

Let’s put it in a simple way . Say for example your automobile runs on LPG, Petrol and Diesel and it would be pretty interesting if you can switch to different engines just by a button shift, isnt it. A MySQL database does the same it 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 engine present in MySQL depends on the way you installed it. To add new engine MySQL should be recompiled. 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.

Refference : http://www.techrepublic.com – Thank You Tech Republic for your awesome article.