Using a combination of InnoDB and MyIsam tables

Question

I have 2 tables in which I want more robustness and fortunately don't require frequent rapid fulls of data and examination. All other tables would inevitably be MyISAM.

Can I safely use both (I've read a handful of discouragements from this) without fearing bugs or data getting affected by differences between engines?

Answer 1

Yes, you can.

Note that only InnoDB tables support transactions and FOREIGN KEY relationships.

Answer 2

The MySQL manual says:

You can freely mix InnoDB tables with tables from other MySQL storage engines, even within the same statement.

Reference: http://dev.mysql.com/doc/refman/5.0/en/innodb.html

Answer 3

You CAN but this introduces a number of disadvantages:

• Your server tuning will now necessarily be a compromise - you cannot use all the memory for either MyISAM OR InnoDB (NB: This does not apply if they are on different servers)
• Replication fails in a number of edge-cases, because if you have a failed transaction which contained changes to some MyISAM tables, it can neither correctly commit it nor roll it back
• You still can't back up your server using MVCC, because you'd still not get a consistent snapshot of MyISAM tables

So basically, I'd encourage you to switch wholly to InnoDB. Then you can pretty much forget about MyISAM and not devote resources to it, and get the full benefits of using InnoDB. Anyone who thinks MyISAM is faster is either not tuning InnoDB correctly, or has such small data that who cares.

MyISAM does faster table scans, but if you're doing those on large tables, you have bigger problems.