08.17.07
A warning on mixing MySQL Engines in the same database
MySQL has a unique feature whereby you can select the storage engine for each table. If you don’t know what a storage engine is, it is responsible for managing the physical data on disk - from data files and indexes to the memory buffers used to access the data and the locking needed to prevent multiple people from clobbering each other’s changes. This is both a boon and a bane for Systems Administrators and DBAs.
It’s a great feature because developers, Sys Admins and DBAs can select the engine with the properties they require. For example, the InnoDB engine supports ACID-compliant transactions, foreign keys and row-level locking. The MyISAM engine, while being lightning fast on reads and supporting concurrent inserts, does not have ACID-compliant transactions or foreign keys and implements table-level locking. Other engines are available, such as memory-based tables, clustered tables, federated tables (reference a table on server B from server A) and more. This level of flexibility can’t be found in any other database server.
This flexibility comes with a stiff price, though. If you mix engines in a single database then you must be aware of several limitations:
- Foreign keys support in the InnoDB engine does not extend to other engines, meaning you can’t have a InnoDB-based table define a foreign key on a MyISAM-based table.
- Likewise, transaction support in the InnoDB engine does not extend to tables defined using another engine.
- Some buffers are engine-specific so you may need more memory than if you used a single engine for all tables.
