Pros and Cons of MySQL Table Types

Of all the positive things that MySQL brings to the table, probably the most overlooked is multiple table types. This facet of the application is overlooked as a feature and more importantly is overlooked at design time.

MySQL has six distinct table types.

• MyISAM
• MERGE
• ISAM
• HEAP
• InnoDB
• BDB or BerkeleyDB Tables

A Database is no fun when you are locked out

The default table type for MySQL is MyISAM. It has table level locking, which means during an UPDATE, nobody can access any other record of the same table. BDB uses Page level locking, and during an UPDATE, nobody can access any other record residing in the same database page of that table, until the locking transaction issues a COMMIT.

InnoDB however, uses Row level locking. Row level locking ensures that during an UPDATE, nobody can access that particular row, until the locking transaction issues a COMMIT. Any of the above table types will probably be fine for a web server, but in a LAN application can cause unecessary issues.

Special circumstances call for special tools (or tables)

MERGE tables are a collection of identical MyISAM tables that can be used as one. You can only SELECT, DELETE, and UPDATE from the collection of tables. If you DROP the MERGE table, you are only dropping the MERGE specification. One reasons why you would use MERGE tables is to get more speed. You can split a big read-only table and then put the different table parts on different disks. You could do more efficient searches. If you know exactly what you are looking for, you can search in just one of the split tables for some queries and use a MERGE table for others. Repairs are more efficient. It's easier to repair the individual files that are mapped to a MERGE file than trying to repair a really big file. MyISAM and therefore MERGE tables are represented as individual files on the harddrive. You can go around the file-size limit for the operating system.

Some of the disadvantages of using MERGE tables are:

• You can only use identical MyISAM tables for a MERGE table.
• REPLACE doesn't work.
• Key reads are slower.

Also, you can't do DROP TABLE, ALTER TABLE, DELETE FROM table_name without a WHERE clause, REPAIR TABLE, TRUNCATE TABLE, OPTIMIZE TABLE, or ANALYZE TABLE on any of the table that is mapped by a MERGE table that is "open". If you do this, the MERGE table may still refer to the original table and you will get unexpected results. The easiest way to get around this deficiency is to issue the FLUSH TABLES command, ensuring no MERGE tables remain "open".

Well, that should make you think twice about using MERGE tables. ISAM tables will disappear in MySQL version 5.0, so it wouldn't be a good idea to use them. Last but not least is the HEAP table type. HEAP tables use hashed indexes and are stored in memory. This makes them very fast, but if MySQL crashes you will lose all data stored in them. They are very useful for temporary tables. HEAP sounds cool but I don't think the risk justifies the performance.

The Lowdown on MySQL Table Types

Most people use MyISAM if they need speed and InnoDB for data integrity. You can use more than one or any combination of these table types in your database. Remember to asses the needs of your application before building it. Even though MyISAM is faster than InnoDB in the MySQL world, InnoDB is fast compared to any database engine. With InnoDB you get transactions, speed and integrity three features not usually used in the same sentence. Most of my customers want as much speed as they can get, but at the end of the day, good data integrity lets them sleep at night.