Pros and Cons of MySQL Table Types

Please visit http://sqldbpool.blogspot.com/

(for more database related articles)

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

Finding a transaction table that's just my type

Two of these table types currently support transactions. Transactions give the user greater control when working with data. You would use syntax similar to the following for a manual transaction.

START TRANSACTION;

SELECT @A:=SUM(salary) FROM table1 WHERE type=1;

UPDATE table2 SET summmary=@A WHERE type=1;

COMMIT;

Of the two commonly used transaction table types, the first is BerkeleyDB transactional tables provided by SleepyCat (www.sleepycat.com). In order to use BDB tables use a binary with BDB support or configure the source with the withberkeleydb option. If you don't want to use BDB tables, start the MySQL server with the skipbdb option. This will save a lot of memory, since the BDB library won't be included. However, you won't be able to use BDB tables. BDB is not used nearly as much as our second alternative which is InnoDB. InnoDB features rowlevel locking, consistent nonlocking read in SELECTs and common tablespace for all tables.

InnoDB Tables are made by Innobase Oy (www.innodb.com), and are distributed under the GPL as well as commercially. If you need commercial support or commercial licenses for your application and cost is a concern, not using InnoDB will save you about 20-50 % for licenses and support contracts. If data integrity is a concern InnoDB provides MySQL with a transactional storage engine and crash recovery capabilities. InnoDB has been designed for maximum performance when processing large data volumes and any other diskbased relational database engine does probably not match CPU efficiency. There are other transactional table types out there (such as Gemini), but they do not seem to be used any more than BDB. So, in a nutshell, most users prefer the speed and features of InnoDB.

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.