The previous writeups are very dismissive of MySQL, but they're also badly misinformed. MySQL is unique in that it allows you to use several completely different types of
tables; you can even mix table types within the same
database. Contrary to what many people would have you believe, MySQL
is ACID-compliant if you use the
InnoDB or
Berkeley DB table types.
MySQL supports the following table types:
- MyISAM
The default MySQL table type. MyISAM tables are extremely fast and very efficient for storing and retrieving large amounts of data very quickly. Each MyISAM table consists of one data file and one index file, which makes MyISAM tables easy to move or back up, since you just need to copy a few files. As of MySQL 4.0, MyISAM tables also support query caching, which makes things very speedy for servers that handle lots of identical select operations.
Unfortunately, MyISAM tables do not support transactions and will be corrupted if the MySQL server dies in the middle of a write. Luckily, MySQL has a very good table repair tool, which will almost always return a corrupted MyISAM table to working order with little or no loss of data.
Coincidentally, Everything2 itself runs off a MySQL database using MyISAM tables.
- MERGE
A MERGE table is a collection of two or more MyISAM tables with identical columns and keys that are merged together and can be used like one big table. This is very useful in situations where you have huge tables or tables that you'd like to split onto separate hard drives for speed purposes, but which you still need to access as one table. MERGE tables are particularly useful for logs, where you may want to compress old data to a low-size, read-only format, but you still need the recent data to be read-write.
- ISAM
Predecessor to MyISAM, included with MySQL mostly for backwards-compatibility, but is slated to disappear in version 4.1. ISAM tables are basically the same as MyISAM tables, except that they are not binary portable across platforms, they can't handle tables larger than four gigabytes, they have smaller key limits, and dynamic tables tend to get more fragmented.
- HEAP
HEAP tables are stored completely in memory, and are thus extremely fast. HEAP tables are excellent for storing temporary data. Just remember that all that data will go away forever when the server shuts down or crashes. It goes without saying that you probably shouldn't create a HEAP table bigger than the amount of available RAM in your server, since that'll just force the data to be swapped to disk, negating all the benefits of being stored in memory. Insert operations are roughly 30% faster on HEAP tables than on MyISAM tables, while selects are only around 15% faster due to MyISAM's excellent query caching features.
- InnoDB
InnoDB tables are robust, transaction-safe and ACID-compliant, with complete commit, rollback and crash-recovery capabilities. They also support foreign key constraints, unlike other MySQL table types, and are designed to process huge amounts of data very efficiently. If you hear someone complaining about MySQL's featureset, they probably haven't discovered InnoDB tables. InnoDB is used on many large production sites, the most recognizable of which is probably Slashdot.
- Berkeley DB
Berkeley DB is a high-performance transactional table type that's been around for quite a while, and is actually used in many other applications than just MySQL. Thanks to its high performance, small footprint, low overhead and good scalability, Berkeley DB is used in lots of switches, routers and other embedded systems.
Why on Earth would anyone need a database with more than one table format, you ask? Let's imagine a scenario. You've been hired to develop an online banking website for the Everything Bank in Everything, Kansas. The bank itself already keeps track of all its transactions using a very beefy, professionally-installed rack of servers running Oracle. For the online banking site, you aren't allowed to mess with this database directly, so you'll have to roll your own, which will then send updates to the master every night.
After doing lots of research, you decide you'd like to use MySQL for the task, due to its support for multiple table types. The most important table in your database is the transaction table, which will keep track of all the money that flows in and out of your users' accounts. You'll want to use InnoDB for this, so that you don't credit someone's account with $5,000 and then fail to debit the source account due to a server crash in the middle of the update.
You also need a table to hold user account information. This needs to be speedy and able to be updated quickly, and must also be easy to back up, although it doesn't need to use transactions because this is just non-volatile data like usernames, addresses, etc. You decide to use MyISAM for this table, which will give you a nice balance of speed, efficiency, and functionality without the overhead of transaction support that you won't be using anyway.
In the interests of security, you'll want to store as little user information as possible in browser cookies. You decide to assign each user a secure id via a cookie, and store any temporary session data in a table on the server. You use a HEAP table for this, because you'll be reading and writing to this table on almost every pageload.
It's important to the bank that you keep a detailed log of site usage, so you configure Apache to use MySQL for logging. Once you've got a few months of log data stored, you'll split old months off into a separate compressed read-only MyISAM table and use a MERGE table to generate reports from all these separate tables as if they were one table, which makes your job lots easier.
And there you have it. You've managed to use most of the table types MySQL supports all in one database, which results in a database optimized for a wide range of uses and well-suited for the main task it was intended for. Of course, most of the time you won't need all these features. In your job as a web developer, you rarely ever really need transaction-safe tables, so you tend to use MyISAM most of the time, which results in much better performance since that unused transaction overhead isn't there.
And oh, what a smart web developer you are.